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

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

die

File size: 4.5 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    }
132    $rss->save($staticdir . '/allpostcodes.rss')
133        or die $!
134}
135
136sub missing_district_stats {
137    my $dbh = shift;
138    my $template = Template->new($tt_config);
139
140    my $sql = <<EOF;
141SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
142WHERE    p.id IS NULL AND d.county NOT IN
143('Northern Ireland', 'All counties')
144ORDER BY d.outward
145EOF
146
147    my $sth = $dbh->prepare($sql); 
148    $sth->execute or die $dbh->errstr;
149    my $districts = $sth->fetchall_arrayref({});
150
151    my $vars = {
152        districts => $districts,
153        updated => ctime(time),
154        count => $sth->rows
155    };
156
157    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
158        or warn $template->error();
159}
160
161sub build_submission_graph {
162    my $dbh = shift;
163    my $chart = Chart::Strip->new(
164        title => 'Postcodes collected over time',
165    );
166
167    $chart->add_data(_getDateStats($dbh), {});
168    open PNG, ">$staticdir/stats/submission_graph.png" or die $!;
169    print PNG $chart->png();
170    close PNG;
171    return 1;
172}
173
1741;
Note: See TracBrowser for help on using the repository browser.