1 | package NPEMap::Postcodes; |
---|
2 | |
---|
3 | use vars qw(@ISA @EXPORT); |
---|
4 | use warnings; |
---|
5 | use strict; |
---|
6 | |
---|
7 | use vars qw($tt_config); |
---|
8 | |
---|
9 | use Template; |
---|
10 | use NPEMap::Config; |
---|
11 | |
---|
12 | require Exporter; |
---|
13 | @ISA = qw(Exporter); |
---|
14 | @EXPORT = qw(build_home_stats); |
---|
15 | |
---|
16 | sub _getAllStats { |
---|
17 | my $dbh = shift; |
---|
18 | my $sql = <<"HERE"; |
---|
19 | SELECT COUNT(*) AS all, |
---|
20 | SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us, |
---|
21 | COUNT(DISTINCT outward) AS all_out, |
---|
22 | COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out |
---|
23 | FROM postcodes WHERE deleted = 'f'; |
---|
24 | HERE |
---|
25 | |
---|
26 | my $sth = $dbh->prepare($sql); |
---|
27 | |
---|
28 | $sth->execute(); |
---|
29 | |
---|
30 | return $sth->fetchrow_hashref; |
---|
31 | } |
---|
32 | |
---|
33 | sub _getDateStats { |
---|
34 | my $dbh = shift; |
---|
35 | my $sql = <<"HERE"; |
---|
36 | SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day, |
---|
37 | COUNT(*) AS all, |
---|
38 | SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us |
---|
39 | FROM postcodes WHERE deleted ='f' |
---|
40 | GROUP BY date_trunc('day', created_at) |
---|
41 | ORDER BY date_trunc('day', created_at); |
---|
42 | |
---|
43 | HERE |
---|
44 | |
---|
45 | my $sth = $dbh->prepare($sql); |
---|
46 | $sth->execute(); |
---|
47 | return $sth->fetchall_arrayref({}); |
---|
48 | } |
---|
49 | |
---|
50 | sub _getLastPostcodes { |
---|
51 | my $dbh = shift; |
---|
52 | my $num = shift; |
---|
53 | my $sql = <<"HERE"; |
---|
54 | SELECT easting, northing, outward, inward, TO_CHAR(created_at, 'DDth Mon HH24:MI')AS date |
---|
55 | FROM postcodes |
---|
56 | WHERE NOT deleted |
---|
57 | ORDER BY created_at DESC LIMIT ? |
---|
58 | HERE |
---|
59 | |
---|
60 | my $sth = $dbh->prepare($sql); |
---|
61 | $sth->execute($num); |
---|
62 | return $sth->fetchall_arrayref({}); |
---|
63 | } |
---|
64 | |
---|
65 | =pod |
---|
66 | |
---|
67 | =head build_home_stats |
---|
68 | |
---|
69 | build_home_stats takes a database handle as a parameter and builds the |
---|
70 | HTML stats for inclusion into the home page |
---|
71 | |
---|
72 | =cut |
---|
73 | |
---|
74 | sub build_home_stats { |
---|
75 | my $dbh = shift; |
---|
76 | my $template = Template->new($tt_config); |
---|
77 | |
---|
78 | my $allStats = _getAllStats($dbh); |
---|
79 | my $lastPostcodes = _getLastPostcodes($dbh, 10); |
---|
80 | |
---|
81 | my $vars = { |
---|
82 | allstats => $allStats, |
---|
83 | lastpostcodes => $lastPostcodes |
---|
84 | }; |
---|
85 | |
---|
86 | $template->process('home_stats.tt', $vars, $staticdir . '/stats.html') |
---|
87 | or warn $template->error(); |
---|
88 | } |
---|
89 | |
---|
90 | 1; |
---|