diff options
Diffstat (limited to 'perl/cw_correspondences_reduced.pl')
| -rw-r--r-- | perl/cw_correspondences_reduced.pl | 183 |
1 files changed, 183 insertions, 0 deletions
diff --git a/perl/cw_correspondences_reduced.pl b/perl/cw_correspondences_reduced.pl new file mode 100644 index 0000000..a2b36ef --- /dev/null +++ b/perl/cw_correspondences_reduced.pl @@ -0,0 +1,183 @@ +#!/usr/bin/perl + +use Text::CSV; +use Data::Dumper; + +# cw_products +# 0 id 1 sku 2 product_name + +# cw_skus +# 0 sku_id 1 sku 2 product_id + +# cw_orders +# 0 order_id 1 date 2 status 3 customer_id + +# cw_order_skus +# 0 id 1 order_id 2 sku_id + +# cw_customers +# 0 customer_id 1 customer_type 2 date_added 3 date_modified 4 first_name 5 last_name +# 6 7 8 9 10 = address + +@products = load_csv("cw_products.csv"); +@skus = load_csv("cw_skus.csv"); +@orders = load_csv("cw_orders.csv"); +@order_skus = load_csv("cw_order_skus.csv"); +@customers = load_csv("cw_customers.csv"); +@order_status = load_csv("cw_order_status.csv"); + +$product_name_lookup = {}; +for $product (@products) { + $sku_name = $product->[1]; + $product_name = $product->[2]; + $product_name_lookup->{ $sku_name } = $product_name; +} + +$customer_name_lookup = {}; +for $customer (@customers) { + $id = $customer->[0]; + $name = $customer->[5] . " " . $customer->[4]; + $customer_name_lookup->{ $id } = $name; +} + +$customer_lookup = {}; +for $customer (@customers) { + $id = $customer->[0]; + $customer_name_lookup->{ $id } = $customer; +} + +$order_status_lookup = {}; +for $os (@order_status) { + $id = $os->[0]; + $name = $os->[1]; + $order_status_lookup->{ $id } = $name; +} + +$sku_id_lookup = {}; +for $sku (@skus) { + $sku_id = $sku->[0]; + $sku_name = $sku->[1]; + # $product_name = $product_name_lookup->{ $sku_name }; + $sku_id_lookup->{ $sku_id } = $sku_name; +} + +$order_customer_lookup = {}; +for $order (@orders) { + $order_id = $order->[0]; + $customer_id = $order->[3]; + # $customer_name = $customer_name_lookup->{ $customer_id }; + $order_customer_lookup->{ $order_id } = $customer_id; +} + +$order_sku_lookup = {}; +for $order_sku (@order_skus) { + $order_id = $order_sku->[1]; + $sku_id = $order_sku->[2]; + $sku_name = $sku_id_lookup->{ $sku_id }; + $customer_id = $order_customer_lookup->{ $order_id }; + if ( not exists($order_sku_lookup->{ $customer_id })) { + $order_sku_lookup->{ $customer_id } = {}; + } + $order_sku_lookup->{ $customer_id }->{ $sku_name } = 1; +} + +# print Dumper( $order_sku_lookup ); + +@report = (); + +for $customer (@customers) { + $has_v1 = 0; + $has_v2 = 0; + $has_v3 = 0; + $has_v4 = 0; + $customer_id = $customer->[0]; + if (! exists($order_sku_lookup->{ $customer_id })) { + next; + } + $customer_skus = $order_sku_lookup->{ $customer_id }; + $r = [ + $customer->[0], + $customer->[2], + $customer->[4], + $customer->[5], + $customer->[6], + $customer->[7], + $customer->[8], + $customer->[9], + $customer->[10], + ]; + if ( exists( $customer_skus->{"STBVOL1"} ) + || exists( $customer_skus->{"301MM"} ) + ) { + $has_v1 = 1 + } + if ( exists( $customer_skus->{"STBVOL2"} ) + || exists( $customer_skus->{"303MM"} ) + || exists( $customer_skus->{"Volumes 2 & 3"} ) + || exists( $customer_skus->{"Volumes 2 & 4"} ) + || exists( $customer_skus->{"Volumes 2, 3 & 4"} ) + ) { + $has_v2 = 1 + } + if ( exists( $customer_skus->{"330MM"} ) + || exists( $customer_skus->{"Volumes 2 & 3"} ) + || exists( $customer_skus->{"Volumes 3 & 4"} ) + || exists( $customer_skus->{"Volumes 2, 3 & 4"} ) + ) { + $has_v3 = 1 + } + if ( exists( $customer_skus->{"340MM"} ) + || exists( $customer_skus->{"Volumes 2 & 4"} ) + || exists( $customer_skus->{"Volumes 3 & 4"} ) + || exists( $customer_skus->{"Volumes 2, 3 & 4"} ) + ) { + $has_v4 = 1 + } + push(@$r, $has_v1 ? "VOLUME 1" : ""); + push(@$r, $has_v2 ? "VOLUME 2" : ""); + push(@$r, $has_v3 ? "VOLUME 3" : ""); + push(@$r, $has_v4 ? "VOLUME 4" : ""); + if ($has_v1 || $has_v2 || $has_v3 || $has_v4) { + print Dumper($r); + push(@report, $r); + } +} + +my $csv = Text::CSV->new ( { binary => 1 } ) # should set binary attribute. + or die "Cannot use CSV: ".Text::CSV->error_diag (); + +$fn = "cv_stb_report_reduced.csv"; +open $fh, ">:encoding(utf8)", $fn or die "$fn: $!"; +for $row (@report) { + $csv->print ($fh, $row); + print $fh "\n"; +} +close $fh or die "$fn: $!"; + + + + + + + + + + + + + +sub load_csv () { + my $filename = shift; + my @rows; + my $csv = Text::CSV->new ( { binary => 1 } ) # should set binary attribute. + or die "Cannot use CSV: ".Text::CSV->error_diag (); + + open my $fh, "<:encoding(utf8)", $filename or die "$filename: $!"; + while ( my $row = $csv->getline( $fh ) ) { + push @rows, $row; + } + $csv->eof or $csv->error_diag(); + close $fh; + return @rows; +} + |