Mercurial > hg > reproctool-df
comparison reproctool.cgi @ 9:6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
author | Dominic Cleal <dominic@computerkb.co.uk> |
---|---|
date | Sun, 25 Jan 2009 13:06:33 +0000 |
parents | e30154411e63 |
children | de10d02dc715 |
comparison
equal
deleted
inserted
replaced
7:e30154411e63 | 9:6b98a17fbe22 |
---|---|
38 | 38 |
39 # If the items string contains the contract info too, strip it out | 39 # If the items string contains the contract info too, strip it out |
40 $str_items = $1 if ($str_items =~ /The container .+ contains the following items:(.+)/); | 40 $str_items = $1 if ($str_items =~ /The container .+ contains the following items:(.+)/); |
41 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/); | 41 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/); |
42 | 42 |
43 my @item_names = split(/\s*,\s*/, $str_items); | |
44 | |
45 # SQL fragment to match all items | |
46 my $sql_typenames = ''; | |
47 $sql_typenames = 'types.typeName = ?' if ($#item_names >= 0); | |
48 $sql_typenames .= " OR types.typeName = ?" foreach (1..$#item_names); | |
49 | |
43 # SQL lookup for reprocessing amounts | 50 # SQL lookup for reprocessing amounts |
44 my $sql_reprocess = <<END; | 51 my $sql_reprocess = <<END; |
45 SELECT | 52 SELECT |
46 types.typeID, | 53 types.typeID, |
47 types.typeName, | 54 types.typeName, |
55 groups.groupName, | |
48 attrs.valueInt, -- metaLevel | 56 attrs.valueInt, -- metaLevel |
49 types.basePrice, | 57 types.basePrice, |
50 graphics.icon, | 58 graphics.icon, |
51 SUM(CASE WHEN m1.requiredTypeID = 34 THEN m1.quantity ELSE 0 END), -- [Tritanium] | 59 SUM(CASE WHEN m1.requiredTypeID = 34 THEN m1.quantity ELSE 0 END), -- [Tritanium] |
52 SUM(CASE WHEN m1.requiredTypeID = 35 THEN m1.quantity ELSE 0 END), -- [Pyerite] | 60 SUM(CASE WHEN m1.requiredTypeID = 35 THEN m1.quantity ELSE 0 END), -- [Pyerite] |
57 SUM(CASE WHEN m1.requiredTypeID = 40 THEN m1.quantity ELSE 0 END), -- [Megacyte] | 65 SUM(CASE WHEN m1.requiredTypeID = 40 THEN m1.quantity ELSE 0 END), -- [Megacyte] |
58 SUM(CASE WHEN m1.requiredTypeID = 11399 THEN m1.quantity ELSE 0 END) -- [Morphite] | 66 SUM(CASE WHEN m1.requiredTypeID = 11399 THEN m1.quantity ELSE 0 END) -- [Morphite] |
59 FROM invTypes types | 67 FROM invTypes types |
60 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633 | 68 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633 |
61 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID | 69 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID |
70 INNER JOIN invGroups groups ON types.groupID = groups.groupID | |
62 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID | 71 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID |
63 WHERE types.typeName = ? | 72 WHERE $sql_typenames |
64 GROUP BY | 73 GROUP BY types.typeID |
65 types.typeID, | 74 ORDER BY groupName ASC, typeName ASC |
66 types.typeName, | |
67 attrs.valueInt, | |
68 types.basePrice | |
69 END | 75 END |
70 my $pre_reprocess = $db->prepare($sql_reprocess); | 76 my $pre_reprocess = $db->prepare($sql_reprocess); |
71 | 77 |
78 # Execute, bring back one row per item | |
79 my ($tid, $tname, $gname, $meta, $basePrice, $icon, | |
80 $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp); | |
81 | |
82 $pre_reprocess->execute(@item_names) or die("Can't lookup items: $DBI::errstr"); | |
83 $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$gname, \$meta, \$basePrice, | |
84 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog, | |
85 \$tnocx, \$tzydr, \$tmega, \$tmorp); | |
86 | |
72 my @output = (); | 87 my @output = (); |
73 for my $sitem (split(/\s*,\s*/, $str_items)) | 88 while ($pre_reprocess->fetch()) |
74 { | 89 { |
75 my ($tid, $tname, $meta, $basePrice, $icon, | 90 my $item = {}; |
76 $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp); | 91 my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) + |
92 ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) + | |
93 ($mega * $tmega) + ($morp * $tmorp); | |
77 | 94 |
78 $pre_reprocess->execute($sitem) or die("Can't lookup $sitem: $DBI::errstr"); | 95 $meta = 0 unless defined $meta; |
79 $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$meta, \$basePrice, | 96 $item = { id => $tid, name => $tname, meta => $meta, icon => $icon, |
80 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog, | 97 price => $basePrice, reprocess => $isk }; |
81 \$tnocx, \$tzydr, \$tmega, \$tmorp); | |
82 | 98 |
83 my $item = {}; | 99 # If this item exists multiple times in the input, then they weren't stacked |
84 if ($pre_reprocess->fetch()) | 100 # so output it multiple times |
85 { | 101 my @matching_in = grep({ $_ eq $tname } @item_names); |
86 my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) + | 102 push @output, $item foreach (0..$#matching_in); |
87 ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) + | |
88 ($mega * $tmega) + ($morp * $tmorp); | |
89 | |
90 $meta = 0 unless defined $meta; | |
91 $item = { id => $tid, name => $tname, meta => $meta, icon => $icon, | |
92 price => $basePrice, reprocess => $isk }; | |
93 } | |
94 push @output, $item; | |
95 } | 103 } |
96 | 104 |
97 my $col = 0; | 105 my $col = 0; |
98 print<<END; | 106 print<<END; |
99 <html> | 107 <html> |