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

Last change on this file since 433 was 433, checked in by Nick Burch, 13 years ago

Calculate the outer + 1st inner stats too

File size: 4.3 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;
13
14require Exporter;
15@ISA    = qw(Exporter);
16@EXPORT = qw(build_home_stats missing_district_stats build_submission_graph);
17
18sub _getAllStats {
19    my $dbh = shift;
20    my $sql = <<"HERE";
21SELECT COUNT(*) AS all,
22       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
23       COUNT(DISTINCT outward) AS all_out,
24       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out,
25       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
26                outward || ' ' || substr(inward,1,1)
27             ELSE '' END) - 1 AS all_out1,
28       COUNT(DISTINCT CASE WHEN char_length(inward) > 0 THEN
29                                CASE WHEN source = 0 THEN
30                        outward || ' ' || substr(inward,1,1)
31                                ELSE '' END
32             ELSE '' END) - 1 AS us_out1
33FROM postcodes WHERE deleted = 'f';
34HERE
35
36    my $sth = $dbh->prepare($sql);
37
38    $sth->execute();
39
40    return $sth->fetchrow_hashref;
41}
42
43sub _getDateStats {
44    my $dbh = shift;
45    my $sql = <<"HERE";
46SELECT extract(epoch FROM date_trunc('day', created_at)) AS time,
47       COUNT(*) AS all,
48       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS value
49FROM postcodes WHERE deleted ='f'
50GROUP BY  date_trunc('day', created_at)
51ORDER BY  date_trunc('day', created_at);
52
53HERE
54
55    my $sth = $dbh->prepare($sql);
56    $sth->execute();
57    return $sth->fetchall_arrayref({});
58}
59
60sub _getLastPostcodes {
61    my $dbh = shift;
62    my $num = shift;
63    my $sql = <<"HERE";
64SELECT easting, northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date
65FROM postcodes
66WHERE NOT deleted
67ORDER BY created_at DESC LIMIT ?
68HERE
69
70    my $sth = $dbh->prepare($sql);
71    $sth->execute($num);
72    return $sth->fetchall_arrayref({});
73}
74
75=pod
76
77=head build_home_stats
78
79build_home_stats takes a database handle as a parameter and builds the
80HTML stats for inclusion into the home page
81
82=cut
83
84sub build_home_stats {
85    my $dbh = shift;
86    my $template = Template->new($tt_config);
87
88    my $allStats = _getAllStats($dbh);
89    my $lastPostcodes = _getLastPostcodes($dbh, 10);
90
91    my $vars = {
92        allstats => $allStats,
93        lastpostcodes => $lastPostcodes
94    };
95   
96    $template->process('home_stats.tt', $vars, $staticdir . '/stats/summary.html')
97        or warn $template->error();
98}
99
100sub missing_district_stats {
101    my $dbh = shift;
102    my $template = Template->new($tt_config);
103
104    my $sql = <<EOF;
105SELECT   d.outward, d.district, d.county FROM districts AS d LEFT OUTER JOIN postcodes AS p ON (d.outward = p.outward)
106WHERE    p.id IS NULL AND d.county NOT IN
107('Northern Ireland', 'All counties', 'Aberdeenshire',
108 'Banffshire, Aberdeenshire', 'Dumfriesshire', 'Kirkcudbrightshire',
109 'Wigtownshire', 'Midlothian', 'West Lothian', 'East Lothian',
110 'Peeblesshire', 'Stirlingshire', 'Clackmannanshire', 'Perthshire',
111 'Dunbartonshire', 'Inverness', 'Ross', 'Morayshire', 'Isle of Skye',
112 'Ayrshire', 'Isle of Arran', 'Isle of Cumbrae', 'Caithness', 'Orkney',
113 'Orkney Islands', 'Fife', 'Lanarkshire', 'Renfrewshire', 'Bute',
114 'Argyllshire', 'Angus & Argyllshire', 'Inverness & Argyllshire',
115 'Renfrewshire', 'Isle of Gigha', 'Isle of Islay', 'Isle of Jura',
116 'Isle of Colonsay', 'Isle of Mull', 'Isle of Iona', 'Isle of Tiree',
117 'Isle of Coll', 'Isle of Barra', 'Isle of South Uist', 'Isle of North Uist',
118 'Isle of Harris', 'Isle of Scalpay', 'Isle of Harris', 'Isle of Lewis',
119 'Isle of Benbecula', 'Perthshire', 'Angus', 'InvernesS', 'Isle of Eigg',
120 'Isle of Rhum', 'Isle of Canna', 'Shetland', 'Sutherland', 'Banffshire',
121 'Roxburghshire', 'Selkirkshire', 'Berwickshire')
122ORDER BY d.outward
123EOF
124
125    my $sth = $dbh->prepare($sql); 
126    $sth->execute or die $dbh->errstr;
127    my $districts = $sth->fetchall_arrayref({});
128
129    my $vars = {
130        districts => $districts,
131        updated => ctime(time),
132        count => $sth->rows
133    };
134
135    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
136        or warn $template->error();
137}
138
139sub build_submission_graph {
140    my $dbh = shift;
141    my $chart = Chart::Strip->new(
142        title => 'Postcodes collected over time',
143    );
144
145    $chart->add_data(_getDateStats($dbh), {});
146    open PNG, ">$staticdir/stats/submission_graph.png" or die $!;
147    print PNG $chart->png();
148    close PNG;
149    return 1;
150}
151
1521;
Note: See TracBrowser for help on using the repository browser.