Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 33 lines (22 sloc) 0.962 kb
a0ce10f @autarch Add a script to get some per country stats
autarch authored
1 use strict;
2 use warnings;
3
4 use VegGuide::Schema;
5 use VegGuide::Location;
6
7 my $dbh = VegGuide::Schema->Schema()->driver()->handle();
8
9 my $country_sql = 'SELECT location_id FROM Location WHERE is_country = 1';
10 my $count_sql_base = q{SELECT COUNT(*) FROM Vendor WHERE creation_datetime >= '2011-01-01' AND creation_datetime < '2012-01-01' AND location_id IN };
11
12 my %count;
13 for my $location ( map { VegGuide::Location->new( location_id => $_ ) }
14 @{ $dbh->selectcol_arrayref($country_sql) } ) {
15
16 my @ids = map { $_->location_id() } $location->descendants();
17 next unless @ids;
18
19 my $count_sql = $count_sql_base;
20 $count_sql .= '(';
21 $count_sql .= join ', ', ('?') x @ids;
22 $count_sql .= ')';
23
24 my $count = $dbh->selectcol_arrayref( $count_sql, undef, @ids );
25
26 $count{ $location->name_with_parent() } = $count ? $count->[0] : 0;
27 }
28
29 for my $name ( sort { $count{$b} <=> $count{$a} } keys %count ) {
30 print "$name - $count{$name}\n";
31 }
32
Something went wrong with that request. Please try again.