Changeset 219

Timestamp:

Oct 29, 2006, 3:16:45 PM (15 years ago)

Author:

Dominic Hargreaves

Message:

Add a perl module for some glue including templates, and modify update_stats.pl
to use it.

Location:

trunk/npemap.org.uk

Files:

• perllib (added)
• perllib/NPEMap (added)
• perllib/NPEMap/Config.pm (added)
• perllib/NPEMap/Postcodes.pm (added)
• scripts/content/update_stats.pl (modified) (3 diffs)
• templates (added)
• templates/home_stats.tt (added)

trunk/npemap.org.uk/scripts/content/update_stats.pl

@@ -7 +7 @@
 use warnings;
 
+use lib qw(/home/dom/working/npemap.org.uk/perllib);
+
 use DBI;
-
+use NPEMap::Postcodes;
 use vars qw($dbname $dbhost $dbuser $dbpass);
 
@@ -24 +26 @@
 
 
-my $sql1 = <<"HERE";
-SELECT COUNT(*) AS all, 
-       SUM(CASE WHEN source = 0 THEN 1 ELSE 0 END) AS us, 
-       COUNT(DISTINCT outward) AS all_out, 
-       COUNT(DISTINCT CASE WHEN source = 0 THEN outward ELSE '' END) - 1 AS us_out 
-FROM postcodes WHERE deleted = 'f';
-HERE
-
-my $sql2 = <<"HERE";
-SELECT TO_CHAR(DATE_TRUNC('day', created_at), 'YYYY-MM-DD') AS day, 
-       COUNT(*) AS all, 
-       SUM(CASE WHEN source=0 THEN 1 ELSE 0 END) AS us 
-FROM postcodes WHERE deleted ='f' 
-GROUP BY  date_trunc('day', created_at) 
-ORDER BY  date_trunc('day', created_at);
-
-HERE
-
     # In case the database went away, make sure we have a connection
     unless (setup_dbh()) {
@@ -47 +31 @@
     }
 
-    my $sth = $dbh->prepare($sql1);
-    if ($sth->execute()) {
-       my @stats = $sth->fetchrow_array();
-       print "allStats(". join(", ", @stats) .")\n";
-    } else {
-        print_internal_err("Database error retrieving data");
-    }
-    $sth->finish;
-
-    
-
-    $sth = $dbh->prepare($sql2);
-    
-    if ($sth->execute()) {
-        my $hr = $sth->fetchall_hashref('day');
-        foreach my $key (keys %$hr) {
-            my $hash = $$hr{$key};
-            print "dateStats('$$hash{day}', $$hash{all}, $$hash{us});\n";
-        }
-    } else {
-        print_internal_err("Database error retrieving data");
-    }
-    $sth->finish;
+build_home_stats($dbh);