Changeset 219


Ignore:
Timestamp:
Oct 29, 2006, 3:16:45 PM (13 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:
6 added
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/scripts/content/update_stats.pl

    r178 r219  
    77use warnings;
    88
     9use lib qw(/home/dom/working/npemap.org.uk/perllib);
     10
    911use DBI;
    10 
     12use NPEMap::Postcodes;
    1113use vars qw($dbname $dbhost $dbuser $dbpass);
    1214
     
    2426
    2527
    26 my $sql1 = <<"HERE";
    27 SELECT 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
    31 FROM postcodes WHERE deleted = 'f';
    32 HERE
    33 
    34 my $sql2 = <<"HERE";
    35 SELECT 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
    38 FROM postcodes WHERE deleted ='f'
    39 GROUP BY  date_trunc('day', created_at)
    40 ORDER BY  date_trunc('day', created_at);
    41 
    42 HERE
    43 
    4428    # In case the database went away, make sure we have a connection
    4529    unless (setup_dbh()) {
     
    4731    }
    4832
    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;
     33build_home_stats($dbh);
    7234
    7335
Note: See TracChangeset for help on using the changeset viewer.