test for ean price lookup

ean_lookup

#!/usr/bin/env perl
use 5.10.0;
use strict;
use Supers::Schema;
use Text::CSV;
use utf8;
use Encode;
use Excel::Writer::XLSX;
use Data::Types;
binmode(STDOUT, ":utf8");
Supers::Schema->init;

# ABSTRACT: script to export available zipcodes and prices for a given product (EAN)

############################################
# create excel workbook, add worksheet and formats
#initialize worksheet row and column counters
my $row = 2;
my $col = 0;

# create excel workbook for output
my $workbook = Excel::Writer::XLSX->new( 'products_porEAN_allzip.xlsx' );
die "Problems creating new Excel file: $!" unless defined $workbook;
#   Add a worksheet to the workbook

my $worksheet = $workbook->add_worksheet();
$worksheet->keep_leading_zeros(); # in case the EAN has leading zeros make sure the number is kept intact
$worksheet->set_header('&LSoysuper - Informe de Precios Por EAN &RActualizado &T'); #worksheet header

# format column widths of the worksheet
$worksheet->set_column( 'A:A', 50 );
$worksheet->set_column( 'B:C', 25 );
$worksheet->set_column( 'D:G', 20 );  
$worksheet->set_column( 'H:J', 25 );
$worksheet->set_column( 'K:N', 20 );

#  define formats to use throughout the worksheet
my $title_format = $workbook->add_format(font => 'Verdana', bold => 1, color => 'orange', align => 'center', size => 12 );
$title_format -> set_bottom(1);
my $cell_format = $workbook->add_format( num_format=> '##.##');
my $euroformat = $workbook->add_format();
$euroformat -> set_num_format( '€0,00' );
#my $format_preciobajo = $workbook->add_format( bold => 1, color => 'green' ); # format for lowest price in range
#########################################

# input variables
my $eaninput = decode('utf8', shift) || die 'Need an EAN!'; # input is 1 product by ean
# EAN to use for testing  8410000807356; 
my $product = Product->find_one({ ean => qr/$eaninput/i }) || die 'EAN not found'; # if EAN is wrong or doesn't exist then die
my @prodname = map( {$product->$_} qw/ name ean /); # get product comon name and write to worksheet
$worksheet->write( 'A1', \@prodname, $title_format );

my @warehouses = $product->warehouses_available->all;

#   create array of second row column headers with special formatting title_format and write it to the worksheet
#   CAREFUL!! order of supermarkets is HARD CODED
my @row2 = ( "Provincia",  "Código Postal", "Mercadona", "Alcampo", "El Corte Inglés", "Carrefour", "Eroski", "Condis");
$worksheet->write( 'A2', \@row2, $title_format );

my @allzips; #final list of all zipcodes where product is available
my @superms = (qw/mercadona alcampo corteingles carrefour eroski condis/); #order of supermarkets is hard coded


for my $wh ( @warehouses ) {
    #my $superavail = $wh->supermarket;
    my @zipcodes = $wh->zipcodes->all;
    push @allzips, @zipcodes;
}

# here I want to sort all zips and eliminate all duplicates, then I can cleanly re-get all wh for each zip (so they come all together) and write data 
#foreach my $value (sort {$allzips{$a} cmp $allzips{$b} } keys @allzips)

    # iterate over every zipcode in warehouses where product is available (we checked earlier)
    for my $zipcod (@allzips) {

        # get warehouses for each zipcode in available warehouses, 
        my @allwarehz = $zipcod->warehouses->all;

        my $provincia = $zipcod->province;
        my $cp = $zipcod->code;

        my @data = ($provincia, $cp);

        #my @superms = map {$_->supermarket} @allwarehz;
        #say @superms;

        for my $wh2 (@allwarehz) {
            
            for my $supr (@superms) {
                #say $supr;
               # if ( my $price = $product->get_price($supr,$wh2) ) {
               #     push @data, $price->[0];
               # }
               # else { 
               #     push @data, " ";
               # }
            }
        }

        # write data to row of worksheet and appy cell format    
        $worksheet->write($row++, 0, \@data );
        $worksheet->set_row( $row, undef, $cell_format );
        
    }   

#close workbook properly
$workbook->close();