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

Last change on this file since 341 was 341, checked in by Dominic Hargreaves, 14 years ago

More

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