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

Last change on this file since 604 was 604, checked in by Dominic Hargreaves, 12 years ago

refactor

File size: 4.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;
12use Chart::Strip;
13use XML::RSS;
14use Date::Format;
15use URI::Escape;
16
17my $iso8601 = "%Y-%m-%dT%H:%M:%SZ";
18
19require Exporter;
20@ISA    = qw(Exporter);
21@EXPORT = qw(build_home_stats missing_district_stats build_submission_graph build_rss);
22
23sub _getAllStats {
24    my $dbh = shift;
25    my $sql = <<"HERE";
26SELECT COUNT(*) AS all,
27       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
28       COUNT(DISTINCT outward) AS all_out,
29       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out,
30       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
31                outward || ' ' || substr(inward,1,1)
32             ELSE '' END) - 1 AS all_out1,
33       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
34                                CASE WHEN source = 0 THEN
35                        outward || ' ' || substr(inward,1,1)
36                                ELSE '' END
37             ELSE '' END) - 1 AS us_out1
38FROM postcodes WHERE deleted = 'f';
39HERE
40
41    my $sth = $dbh->prepare($sql);
42
43    $sth->execute();
44
45    return $sth->fetchrow_hashref;
46}
47
48sub _getDateStats {
49    my $dbh = shift;
50    my $sql = <<"HERE";
51SELECT extract(epoch FROM date_trunc('day', created_at)) AS time,
52       COUNT(*) AS all,
53       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS value
54FROM postcodes WHERE deleted ='f'
55GROUP BY  date_trunc('day', created_at)
56ORDER BY  date_trunc('day', created_at);
57
58HERE
59
60    my $sth = $dbh->prepare($sql);
61    $sth->execute();
62    return $sth->fetchall_arrayref({});
63}
64
65sub _getLastPostcodes {
66    my $dbh = shift;
67    my $num = shift;
68    my $sql = <<"HERE";
69SELECT easting, northing, ie_easting, ie_northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date, TO_CHAR(created_at, 'YYYY-MM-DD"T"HH24:MI:SSTZ') AS iso_date
70FROM postcodes
71WHERE NOT deleted
72ORDER BY created_at DESC LIMIT ?
73HERE
74
75    my $sth = $dbh->prepare($sql);
76    $sth->execute($num);
77    return $sth->fetchall_arrayref({});
78}
79
80=pod
81
82=head build_home_stats
83
84build_home_stats takes a database handle as a parameter and builds the
85HTML stats for inclusion into the home page
86
87=cut
88
89sub build_home_stats {
90    my $dbh = shift;
91    my $template = Template->new($tt_config);
92
93    my $allStats = _getAllStats($dbh);
94    my $lastPostcodes = _getLastPostcodes($dbh, 10);
95
96    my $vars = {
97        allstats => $allStats,
98        lastpostcodes => $lastPostcodes
99    };
100   
101    $template->process('home_stats.tt', $vars, $staticdir . '/stats/summary.html')
102        or warn $template->error();
103}
104
105sub build_rss {
106    my $dbh = shift;
107    my $template = Template->new($tt_config);
108
109    my $lastPostcodes = _getLastPostcodes($dbh, 100);
110
111    my $rss = new XML::RSS (version => '1.0');
112    $rss->channel(
113        title => 'NPEmap - all postcodes',
114        link  => 'http://www.npemap.org.uk/',
115        description => 'All postcodes recently submitted to NPEmap',
116        dc => {
117            date => time2str($iso8601, time, "UTC"),
118            creator => 'webmaster@npemap.org.uk',
119        },
120    );
121
122    foreach my $postcode (@{$lastPostcodes}) {
123        my $postcode_text = $postcode->{outward} . ' ' .
124                            $postcode->{inward} || '';
125        $rss->add_item(
126            title => $postcode_text,
127            description => $postcode_text,
128            link => 'http://www.npemap.org.uk/?' . uri_escape($postcode_text),
129            dc => {
130                date => $postcode->{iso_date}
131            }
132        );
133    }
134    $rss->save($staticdir . '/allpostcodes.rss')
135        or die $!
136}
137
138sub missing_district_stats {
139    my $dbh = shift;
140    my $template = Template->new($tt_config);
141
142    my $sql = <<EOF;
143SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
144WHERE    p.id IS NULL AND d.county NOT IN
145('Northern Ireland', 'All counties')
146ORDER BY d.outward
147EOF
148
149    my $sth = $dbh->prepare($sql); 
150    $sth->execute or die $dbh->errstr;
151    my $districts = $sth->fetchall_arrayref({});
152
153    my $vars = {
154        districts => $districts,
155        updated => ctime(time),
156        count => $sth->rows
157    };
158
159    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
160        or warn $template->error();
161}
162
163sub build_submission_graph {
164    my $dbh = shift;
165    my $chart = Chart::Strip->new(
166        title => 'Postcodes collected over time',
167    );
168
169    $chart->add_data(_getDateStats($dbh), {});
170    open PNG, ">$staticdir/stats/submission_graph.png" or die $!;
171    print PNG $chart->png();
172    close PNG;
173    return 1;
174}
175
1761;
Note: See TracBrowser for help on using the repository browser.