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

Last change on this file since 353 was 353, checked in by Dominic Hargreaves, 13 years ago

Date stats as graph

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