[calc]
%sku = (
[loop search="
ra=yes
fi=merchandising
ml=100000
rf=sku,others_bought
"][loop-calc]
my $ref = q[[loop-data merchandising others_bought]];
return '' if $out !~ m/^\s*{/;
return < $ref,
EOF
[/loop-calc]
[/loop]
)
[/calc]
[perl tables="__UI_META_TABLE__ orderline merchandising"]
my $db = $Db{orderline};
return "NO ORDERLINE table???!" if ! $db;
my $start = tag_data( qw/__UI_META_TABLE__ lookup_exclude orderline/ );
my $query = "select code,order_number,sku,username from orderline ";
if($start) {
$query .= " where order_number > '$start'";
}
else {
%sku = ();
}
my %on; # Keep track by order number
my %un; # Keep track by username
my %times_ordered; # Keep track by sku
my $ary = $db->query( { sql => $query } );
for(@$ary) {
my ($line, $on, $sku, $un) = @$_;
push @{$un{$un} ||= []}, $sku;
$times_ordered{$sku}++;
}
for(keys %un) {
my $ref = $un{$_};
@ary = @$ref;
for(@ary) {
my $sku = $_;
$sku{$sku} = {} unless $sku{$sku};
for (@ary) {
next if $_ eq $sku;
$sku{$sku}->{$_}++;
}
}
}
my $mdb = $Db{merchandising};
for(sort keys %sku) {
my $val = $Tag->uneval( { ref => $sku{$_} } );
$out .= "$_: times ordered=$times_ordered{$_} others_bought ==> $val \n";
$mdb->set_field($_, 'others_bought', $val );
$mdb->set_field($_, 'times_ordered', $times_ordered{$_} );
}
return $out;
[/perl]
|