test for ean price lookup

ean_lookup.pl

#!/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 );

#   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 and should keep the same order as in @supers !!!
my @supers = (qw/mercadona alcampo corteingles carrefour eroski condis/); #order of supermarkets is hard coded
$worksheet->write(.
    'A2',.
    [ "Provincia", "Código Postal", "Mercadona", "Alcampo", "El Corte Inglés", "Carrefour", "Eroski", "Condis" ],
    $title_format.
);

# Precalculate all data available for this product
my $data = {};
for my $wh ($product->warehouses_available->all ) { 
    my $price = $product->get_price($wh);
    for my $zip ( $wh->zipcodes->all ) { 
        $data->{$zip->code}{province} = $zip->province;
        $data->{$zip->code}{price}{$wh->supermarket} = $price;
    } 
}

# All ready to dump the data into the worksheet!
for my $zipcode ( sort keys %$data ) { 
    my $zipdata = $data->{$zipcode};
    my @datarow = ( $zipdata->{province}, $zipcode );
    for my $super ( @supers ) { 
        if ( exists $zipdata->{price}{$super} ) { 
            push @datarow, $zipdata->{price}{$super}[0];
        } 
        else { 
            push @datarow, 'na';
        } 
    } 

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

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