chiark
/
gitweb
/
~yarrgweb
/
ypp-sc-tools.web-live.git
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
Can actually update the database
[ypp-sc-tools.web-live.git]
/
pctb
/
commod-email-processor
diff --git
a/pctb/commod-email-processor
b/pctb/commod-email-processor
index b7883fc69e574e9331c30c80ff76cf810bb68f77..8a9bac404a14a9f37a02014e447b3ba7a56cf333 100755
(executable)
--- a/
pctb/commod-email-processor
+++ b/
pctb/commod-email-processor
@@
-56,6
+56,8
@@
setlocale(LC_CTYPE, "en_GB.UTF-8");
my $parser= new MIME::Parser;
our $entity;
my $parser= new MIME::Parser;
our $entity;
+$|=1;
+
sub find_part ($$$) {
my ($filename, $type, $accepter) = @_;
foreach my $part ($entity->parts()) {
sub find_part ($$$) {
my ($filename, $type, $accepter) = @_;
foreach my $part ($entity->parts()) {
@@
-132,37
+134,53
@@
sub main () {
$islandid, $mid,
map { $md{$_} } (qw(timestamp clientspec serverspec)));
$islandid, $mid,
map { $md{$_} } (qw(timestamp clientspec serverspec)));
- my %sth_cs;
- foreach my $cs (qw(commod stall)) {
- $sth_cs{$cs}= $dbh->prepare(
+ my $sth_stall= $dbh->prepare(
"INSERT OR IGNORE
"INSERT OR IGNORE
- INTO
${cs}
s
- (
${cs}id) VALUES (
?)
+ INTO
stall
s
+ (
islandid, stallname) VALUES (?,
?)
");
");
- }
- my %sth_bs;
+ my @v;
+
+ my %sub_bs;
foreach my $bs (qw(buy sell)) {
foreach my $bs (qw(buy sell)) {
-
$sth_bs{$bs}
= $dbh->prepare(
+
my $sth
= $dbh->prepare(
"INSERT INTO $bs
"INSERT INTO $bs
- (commodid,
islandid, stall
id, price, qty)
+ (commodid,
stallid, island
id, price, qty)
VALUES (
(SELECT commodid FROM commods WHERE commodname = ?),
VALUES (
(SELECT commodid FROM commods WHERE commodname = ?),
- $islandid,
- (SELECT stallid FROM stalls WHERE stallname = ?),
- ?, ?
+ (SELECT stallid FROM stalls WHERE stallname = ?),
+ ?, ?, ?
)
");
)
");
+ $sub_bs{$bs}= sub {
+ my ($priceix) = @_;
+ my $price= $v[$priceix]; return if !length $price;
+ my $qty= $v[$priceix+1];
+ $qty++ if $qty =~ s/^\>//;
+ $sth->execute(@v[0..1],$islandid,$price,$qty);
+ }
}
while (<$pt>) {
}
while (<$pt>) {
- my @v= check_tsv_line($_, \&bad_data_fail);
+ @v= check_tsv_line($_, \&bad_data_fail);
+
+ $sth_stall->execute($islandid, $v[1]);
-
+ &{$sub_bs{'buy'}}(2);
+ &{$sub_bs{'sell'}}(4);
+
+# print ".";
}
pipethrough_run_finish($pt, 'gunzip <$deduped_tsv.gz');
}
pipethrough_run_finish($pt, 'gunzip <$deduped_tsv.gz');
+ print "\n";
+ $dbh->commit();
+
+ # select * from ((buy natural join commods) natural join stalls) natural join islands;
+ # select * from ((sell natural join commods) natural join stalls) natural join islands;
+
}
my $ok= eval {
}
my $ok= eval {