pricecharts

track prices of consumer electronics
Log | Files | Refs | README

commit 12f4fad163330f41969f06554795bff573a0a76c
parent 0bb0e7d39c8c0bea80ad0eef72800c3815201fcd
Author: kyle <kyle@getaddrinfo.net>
Date:   Tue,  3 Nov 2015 20:53:03 -0700

html: move stale retailer lookup back to table join

- retailers don't have an svg_stale column
  - staleness can't be looked up directly
- move stale retailer check back to using a table join with products
- this lets retailer lookup work, and it's reasonably efficient (we'll see)

Diffstat:
Mps_html | 15++++++++++-----
1 file changed, 10 insertions(+), 5 deletions(-)

diff --git a/ps_html b/ps_html @@ -34,7 +34,7 @@ my $config = { my $www = Template->new($config) || die Template->error(), "\n"; my $and_stale = $args{a} ? "" : "and products.svg_stale = 1"; -my $where_stale = $args{a} ? "" : "where svg_stale = 1"; +my $where_stale = $args{a} ? "" : "where products.svg_stale = 1"; my $desc_sth = $dbh->prepare(qq{select description from descriptions where manufacturer = ? and part_num = ? order by date}); @@ -79,9 +79,14 @@ my $manufacturer_list = $dbh->selectall_hashref($coarse_sql, \@key_fields); # # retailers # -$stale_list = qq{select distinct retailer from prices $where_stale}; +$stale_list = qq{select distinct prices.retailer from prices, products where + products.manufacturer = prices.manufacturer and + products.part_num = prices.part_num $and_stale}; -$types = qq{select distinct manufacturer from prices where retailer = ? $and_stale}; +$types = qq{select distinct prices.manufacturer from prices, products where + prices.retailer = ? $and_stale and + products.manufacturer = prices.manufacturer and + products.part_num = prices.part_num}; $products_fine = qq{select distinct manufacturer, part_num from prices where manufacturer = ? and retailer = ?}; @@ -220,7 +225,7 @@ my $retailer_info = $dbh->selectall_hashref(qq{select name, url, color from retailers}, "name"); my $parts_sth = $dbh->prepare(qq{select distinct manufacturer, - part_num from prices $where_stale}); + part_num from products $where_stale}); $parts_sth->execute(); my $rendered = 0; @@ -341,7 +346,7 @@ sub make_svg my $width = $right + $center + $left; my $height = $top + $middle + $bottom; - my ($x_min, $x_max, $y_min, $y_max) = (100000000000, 0, 1000000, 0); + my ($x_min, $x_max, $y_min, $y_max) = (100000000000, 0, 1000000, 0.00001); while (my ($retailer, $values) = each %$series) { for (keys %{$values}) { my ($x, $y) = ($_, $values->{$_}{price});