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

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

Also get ie e+n

File size: 3.4 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, ie_easting, ie_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')
108ORDER BY d.outward
109EOF
110
111    my $sth = $dbh->prepare($sql); 
112    $sth->execute or die $dbh->errstr;
113    my $districts = $sth->fetchall_arrayref({});
114
115    my $vars = {
116        districts => $districts,
117        updated => ctime(time),
118        count => $sth->rows
119    };
120
121    $template->process('missing_district_stats.tt', $vars, $staticdir . '/stats/missing_district_stats.html')
122        or warn $template->error();
123}
124
125sub build_submission_graph {
126    my $dbh = shift;
127    my $chart = Chart::Strip->new(
128        title => 'Postcodes collected over time',
129    );
130
131    $chart->add_data(_getDateStats($dbh), {});
132    open PNG, ">$staticdir/stats/submission_graph.png" or die $!;
133    print PNG $chart->png();
134    close PNG;
135    return 1;
136}
137
1381;
Note: See TracBrowser for help on using the repository browser.