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

Last change on this file since 645 was 645, checked in by Nick Burch, 11 years ago

Improve the RSS feed

File size: 6.3 KB
Line 
1# Copyright (c) 2006 Dominic Hargreaves
2# Permission is hereby granted, free of charge, to any person obtaining a
3# copy of this software and associated documentation files (the "Software"),
4# to deal in the Software without restriction, including without limitation
5# the rights to use, copy, modify, merge, publish, distribute, sublicense,
6# and/or sell copies of the Software, and to permit persons to whom the
7# Software is furnished to do so, subject to the following conditions:
8#
9# The above copyright notice and this permission notice shall be included in
10# all copies or substantial portions of the Software.
11#
12# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
13# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
14# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
15# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
16# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
17# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
18# IN THE SOFTWARE.
19#
20package NPEMap::Postcodes;
21
22use vars qw(@ISA @EXPORT);
23use warnings;
24use strict;
25
26use vars qw($tt_config);
27
28use Template;
29use NPEMap::Config;
30use NPEMap::GeoConverter;
31use Date::Format;
32use Chart::Strip;
33use XML::RSS;
34use Date::Format;
35use URI::Escape;
36
37my $iso8601 = "%Y-%m-%dT%H:%M:%SZ";
38
39require Exporter;
40@ISA    = qw(Exporter);
41@EXPORT = qw(build_home_stats missing_district_stats build_submission_graph build_rss);
42
43sub _getAllStats {
44    my $dbh = shift;
45    my $sql = <<"HERE";
46SELECT COUNT(*) AS all,
47       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
48       COUNT(DISTINCT outward) AS all_out,
49       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out,
50       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
51                outward || ' ' || substr(inward,1,1)
52             ELSE '' END) - 1 AS all_out1,
53       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
54                                CASE WHEN source = 0 THEN
55                        outward || ' ' || substr(inward,1,1)
56                                ELSE '' END
57             ELSE '' END) - 1 AS us_out1
58FROM postcodes WHERE deleted = 'f';
59HERE
60
61    my $sth = $dbh->prepare($sql);
62
63    $sth->execute();
64
65    return $sth->fetchrow_hashref;
66}
67
68sub _getDateStats {
69    my $dbh = shift;
70    my $sql = <<"HERE";
71SELECT extract(epoch FROM date_trunc('day', created_at)) AS time,
72       COUNT(*) AS all,
73       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS value
74FROM postcodes WHERE deleted ='f'
75GROUP BY  date_trunc('day', created_at)
76ORDER BY  date_trunc('day', created_at);
77
78HERE
79
80    my $sth = $dbh->prepare($sql);
81    $sth->execute();
82    return $sth->fetchall_arrayref({});
83}
84
85sub _getLastPostcodes {
86    my $dbh = shift;
87    my $num = shift;
88    my $sql = <<"HERE";
89SELECT 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
90FROM postcodes
91WHERE NOT deleted
92ORDER BY created_at DESC LIMIT ?
93HERE
94
95    my $sth = $dbh->prepare($sql);
96    $sth->execute($num);
97    return $sth->fetchall_arrayref({});
98}
99
100=pod
101
102=head build_home_stats
103
104build_home_stats takes a database handle as a parameter and builds the
105HTML stats for inclusion into the home page
106
107=cut
108
109sub build_home_stats {
110    my $dbh = shift;
111    my $template = Template->new($tt_config);
112
113    my $allStats = _getAllStats($dbh);
114    my $lastPostcodes = _getLastPostcodes($dbh, 10);
115
116    my $vars = {
117        allstats => $allStats,
118        lastpostcodes => $lastPostcodes
119    };
120   
121    $template->process('home_stats.tt', $vars, $staticdir . '/stats/summary.html')
122        or warn $template->error();
123}
124
125sub build_rss {
126    my $dbh = shift;
127    my $template = Template->new($tt_config);
128
129    my $lastPostcodes = _getLastPostcodes($dbh, 100);
130
131    my $rss = new XML::RSS (version => '1.0');
132    $rss->add_module(prefix=>'geo', uri=>'http://www.w3.org/2003/01/geo/wgs84_pos');
133    $rss->add_module(prefix=>'os', uri=>'http://downlode.org/Code/RDF/Ordnance_Survey/schema/1');
134    $rss->channel(
135        title => 'NPEmap - all postcodes',
136        link  => 'http://www.npemap.org.uk/',
137        description => 'All postcodes recently submitted to NPEmap',
138        dc => {
139            date => time2str($iso8601, time, "UTC"),
140            creator => 'webmaster@npemap.org.uk',
141        },
142    );
143
144    foreach my $postcode (@{$lastPostcodes}) {
145        my $postcode_text = $postcode->{outward} . ' ' .
146                            $postcode->{inward} || '';
147        my ($lat,$long) = eastingNorthingToLatLong(
148              $postcode->{easting},$postcode->{northing},$postcode->{grid});
149
150        $rss->add_item(
151            title => $postcode_text,
152            description => $postcode_text,
153            link => 'http://www.npemap.org.uk/?' . uri_escape($postcode_text),
154            dc => {
155                date => $postcode->{iso_date}
156            },
157            geo => {
158                lat => sprintf("%0.6f", $lat),
159                long => $long = sprintf("%0.6f", $long),
160            },
161            os => {
162                x => sprintf("%d", $postcode->{easting}),
163                y => $long = sprintf("%d", $postcode->{northing}),
164            },
165        );
166    }
167    $rss->save($staticdir . '/allpostcodes.rss')
168        or die $!
169}
170
171sub missing_district_stats {
172    my $dbh = shift;
173    my $template = Template->new($tt_config);
174
175    my $sql = <<EOF;
176SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
177WHERE    p.id IS NULL AND d.county NOT IN
178('Northern Ireland', 'All counties')
179ORDER BY d.outward
180EOF
181
182    my $sth = $dbh->prepare($sql); 
183    $sth->execute or die $dbh->errstr;
184    my $districts = $sth->fetchall_arrayref({});
185
186    my $vars = {
187        districts => $districts,
188        updated => ctime(time),
189        count => $sth->rows
190    };
191
192    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
193        or warn $template->error();
194}
195
196sub build_submission_graph {
197    my $dbh = shift;
198    my $chart = Chart::Strip->new(
199        title => 'Postcodes collected over time',
200    );
201
202    $chart->add_data(_getDateStats($dbh), {});
203    open PNG, ">$staticdir/stats/submission_graph.png" or die $!;
204    print PNG $chart->png();
205    close PNG;
206    return 1;
207}
208
2091;
Note: See TracBrowser for help on using the repository browser.