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

Last change on this file since 333 was 333, checked in by Dominic Hargreaves, 14 years ago

Add missing district stats

File size: 2.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;
11use Date::Format;
12
13require Exporter;
14@ISA    = qw(Exporter);
15@EXPORT = qw(build_home_stats missing_district_stats);
16
17sub _getAllStats {
18    my $dbh = shift;
19    my $sql = <<"HERE";
20SELECT COUNT(*) AS all,
21       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
22       COUNT(DISTINCT outward) AS all_out,
23       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out
24FROM postcodes WHERE deleted = 'f';
25HERE
26
27    my $sth = $dbh->prepare($sql);
28
29    $sth->execute();
30
31    return $sth->fetchrow_hashref;
32}
33
34sub _getDateStats {
35    my $dbh = shift;
36    my $sql = <<"HERE";
37SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day,
38       COUNT(*) AS all,
39       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us
40FROM postcodes WHERE deleted ='f'
41GROUP BY  date_trunc('day', created_at)
42ORDER BY  date_trunc('day', created_at);
43
44HERE
45
46    my $sth = $dbh->prepare($sql);
47    $sth->execute();
48    return $sth->fetchall_arrayref({});
49}
50
51sub _getLastPostcodes {
52    my $dbh = shift;
53    my $num = shift;
54    my $sql = <<"HERE";
55SELECT easting, northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date
56FROM postcodes
57WHERE NOT deleted
58ORDER BY created_at DESC LIMIT ?
59HERE
60
61    my $sth = $dbh->prepare($sql);
62    $sth->execute($num);
63    return $sth->fetchall_arrayref({});
64}
65
66=pod
67
68=head build_home_stats
69
70build_home_stats takes a database handle as a parameter and builds the
71HTML stats for inclusion into the home page
72
73=cut
74
75sub build_home_stats {
76    my $dbh = shift;
77    my $template = Template->new($tt_config);
78
79    my $allStats = _getAllStats($dbh);
80    my $lastPostcodes = _getLastPostcodes($dbh, 10);
81
82    my $vars = {
83        allstats => $allStats,
84        lastpostcodes => $lastPostcodes
85    };
86   
87    $template->process('home_stats.tt', $vars, $staticdir . '/stats.html')
88        or warn $template->error();
89}
90
91sub missing_district_stats {
92    my $dbh = shift;
93    my $template = Template->new($tt_config);
94
95    my $sql = <<EOF;
96SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
97WHERE    p.id IS NULL
98ORDER BY d.outward
99EOF
100
101    my $sth = $dbh->prepare($sql); 
102    $sth->execute or die $dbh->errstr;
103    my $districts = $sth->fetchall_arrayref({});
104
105    my $vars = {
106        districts => $districts,
107        updated => ctime(time)
108    };
109
110    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
111        or warn $template->error();
112}
113
1141;
Note: See TracBrowser for help on using the repository browser.