source: trunk/npemap.org.uk/scripts/content/update_stats.pl @ 178

Last change on this file since 178 was 178, checked in by David Sheldon, 14 years ago

Script to output some stats. Run every so often to a file that can be
loaded on the front page.

  • Property svn:executable set to *
File size: 2.3 KB
Line 
1#!/usr/bin/perl
2#
3# Copyright (c) 2006 Dominic Hargreaves
4# See accompanying file "LICENCE" for licence details
5
6use strict;
7use warnings;
8
9use DBI;
10
11use vars qw($dbname $dbhost $dbuser $dbpass);
12
13sub print_err;
14sub setup_dbh;
15
16# Read in database config
17my $config = 'npemap.conf';
18do $config or die "Can't read $config!\n";
19
20
21# No point in handling errors here since they'll get handled by the request
22# handler
23my $dbh;
24
25
26my $sql1 = <<"HERE";
27SELECT COUNT(*) AS all,
28       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us,
29       COUNT(DISTINCT outward) AS all_out,
30       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out
31FROM postcodes WHERE deleted = 'f';
32HERE
33
34my $sql2 = <<"HERE";
35SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day,
36       COUNT(*) AS all,
37       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us
38FROM postcodes WHERE deleted ='f'
39GROUP BY  date_trunc('day', created_at)
40ORDER BY  date_trunc('day', created_at);
41
42HERE
43
44    # In case the database went away, make sure we have a connection
45    unless (setup_dbh()) {
46        print_internal_err('Error setting up database connection');
47    }
48
49    my $sth = $dbh->prepare($sql1);
50    if ($sth->execute()) {
51       my @stats = $sth->fetchrow_array();
52       print "allStats(". join(", ", @stats) .")\n";
53    } else {
54        print_internal_err("Database error retrieving data");
55    }
56    $sth->finish;
57
58   
59
60    $sth = $dbh->prepare($sql2);
61   
62    if ($sth->execute()) {
63        my $hr = $sth->fetchall_hashref('day');
64        foreach my $key (keys %$hr) {
65            my $hash = $$hr{$key};
66            print "dateStats('$$hash{day}', $$hash{all}, $$hash{us});\n";
67        }
68    } else {
69        print_internal_err("Database error retrieving data");
70    }
71    $sth->finish;
72
73
74# No more requests to serve, so tidy up
75$dbh->disconnect;
76
77# Helper routines
78sub print_err {
79    my $err = shift;
80    print header("text/plain", "400 $err");
81    print "Error: $err\n";
82}
83
84sub print_internal_err {
85    my $err = shift;
86    print header("text/plain", "500 $err");
87    print "Error: $err\n";
88}
89
90sub setup_dbh {
91    # $dbh is global
92    my $data_source = "dbi:Pg:dbname=$dbname";
93    $data_source .= ";host=$dbhost" if $dbhost;
94    return $dbh = DBI->connect_cached($data_source, $dbuser, $dbpass);
95}
Note: See TracBrowser for help on using the repository browser.