Changeset 219
- Timestamp:
- Oct 29, 2006, 3:16:45 PM (14 years ago)
- 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 7 7 use warnings; 8 8 9 use lib qw(/home/dom/working/npemap.org.uk/perllib); 10 9 11 use DBI; 10 12 use NPEMap::Postcodes; 11 13 use vars qw($dbname $dbhost $dbuser $dbpass); 12 14 … … 24 26 25 27 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_out31 FROM postcodes WHERE deleted = 'f';32 HERE33 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 us38 FROM postcodes WHERE deleted ='f'39 GROUP BY date_trunc('day', created_at)40 ORDER BY date_trunc('day', created_at);41 42 HERE43 44 28 # In case the database went away, make sure we have a connection 45 29 unless (setup_dbh()) { … … 47 31 } 48 32 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; 33 build_home_stats($dbh); 72 34 73 35
Note: See TracChangeset
for help on using the changeset viewer.