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

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

rss

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