#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2009 RoyaltyShare, Inc.      All Rights Reserved
#---------------------------------------------------------------
package Support::GenreMap::Exporter::Excel;

use strict;
use Spreadsheet::WriteExcel;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::XMLObject;
use Common::Assert;
use Common::Log;

use lib '/app/tools/support/lib';
use Support::GenreMap::Translator;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Genre;

use lib '/app/tools/distribution/lib';
use Distribution::DB::Item::DistributionService;
use Distribution::DB::Item::GenreServiceMap;

use base 'Common::XMLObject';

sub ExportToVariable {
    my ($class) = @_;

    # We're going to use some magic to open a filehandle to a variable.
    #
    open my $fh, '>', \my $str or die "Failed to open filehandle: $!";

    my $workbook = Spreadsheet::WriteExcel->new($fh);

    my $worksheet = $workbook->add_worksheet();
    $worksheet->add_write_handler( qr[\w], \&store_string_widths );

    ###############################################################################
    #
    # Add a handler to store the width of the longest string written to a column.
    # We use the stored width to simulate an autofit of the column widths.
    #
    # You should do this for every worksheet you want to autofit.
    #
    $worksheet->add_write_handler( qr[\w], \&store_string_widths );

    # Fetch the genre mappings - We'll have these keyed by service name, I believe;
    #
    my $genreMap = _getGenreMap();

    # !!! There is some wackiness here with column indexes I don't understand.
    # So I'll just work it out...
    #
    my $formatBold;
    $formatBold = $workbook->add_format();
    $formatBold->set_bold();
    my $row = 0;
    foreach my $line (@$genreMap) {
        my $format;
        if ( 0 == $row ) {
            $format = $formatBold;
        }
        for ( my $i = 0 ; $i < @$line ; $i++ ) {
            my $cell = $line->[$i];
            $worksheet->write_string( $row, $i, $cell, $format );
        }
        $row++;
    }

    $workbook->close();

    return $str;
}

sub _getGenreMap {

    # First, read _all_ possible service names.
    #
    my %nameToID;
    my $allServices = Distribution::DB::Item::DistributionService->GetAll();
    while ( my $service = $allServices->next() ) {
        my $id   = $service->service_id;
        my $name = $service->service_name;
        $nameToID{$name} = $id;
    }

    my @genreMap;
    my @sortedNames = sort { lc($a) cmp lc($b) } keys %nameToID;

    my @headers = ( 'Genre ID', 'RS Genre Name' );
    push @headers, @sortedNames;

    push @genreMap, \@headers;

    # Now we read in the actual genre mappings.
    #
    my $allGenres = RPS::DB::Item::Genre->GetAll();
    while ( my $genreData = $allGenres->next() ) {
        my @row;

        # First we'll put the genre id and our genre name into the row.
        #
        my $genreID   = $genreData->genre_id;
        my $genreName = $genreData->genre_name;
        push @row, $genreID;
        push @row, $genreName;

        # Now we loop through the services, and see if they have a mapping for this genre.
        #
        foreach my $serviceName (@sortedNames) {
            my $serviceID = $nameToID{$serviceName};

            my $serviceGenre   = '';
            my $serviceMapping = Distribution::DB::Item::GenreServiceMap->Lookup(
                genre_id   => $genreID,
                service_id => $serviceID,
            );
            if ($serviceMapping) {
                $serviceGenre = $serviceMapping->genre_name;
            }
            push @row, $serviceGenre;
        }
        push @genreMap, \@row;
    }

    #    my $genreServiceMappings = Distribution::DB::Item::GenreServiceMap->GetAll();
    #    while (my $map = $genreServiceMappings->next())
    #    {
    #    }

    Common::Log::Debug( "GENRE MAP: " . Dumper( \@genreMap ) );
    return \@genreMap;
}

##############################################################################
#
# Simuluate Excel's autofit for colums widths.
#
#
# Excel provides a function called Autofit (Format->Columns->Autofit) that
# adjusts column widths to match the length of the longest string in a column.
# Excel calculates these widths at run time when it has access to information
# about string lengths and font information. This function is *not* a feature
# of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.
#
# However, we can make an attempt to simulate it by keeping track of the
# longest string written to each column and then adjusting the column widths
# prior to closing the file.
#
# We keep track of the longest strings by adding a handler to the write()
# function. See add_handler() in the S::WE docs for more information.
#
# The main problem with trying to simulate Autofit lies in defining a
# relationship between a string length and its width in a arbitrary font and
# size. We use two aproaches below. The first is a simple direct relationship
# obtrained by trial and error. The second is a slightly more sophisticated
# method using an external module. For more complicated applications you will
# probably have to work out your own methods.
#
# reverse(''), May 2006, John McNamara, jmcnamara@cpan.org
#

###############################################################################
###############################################################################
#
# Functions used for Autofit.
#

###############################################################################
#
# Adjust the column widths to fit the longest string in the column.
#
sub autofit_columns {

    my $worksheet = shift;
    my $col       = 0;

    for my $width ( @{ $worksheet->{__col_widths} } ) {
        $worksheet->set_column( $col, $col, $width ) if $width;
        $col++;
    }
}

###############################################################################
#
# The following function is a callback that was added via add_write_handler()
# above. It modifies the write() function so that it stores the maximum
# unwrapped width of a string in a column.
#
sub store_string_widths {

    my $worksheet = shift;
    my $col       = $_[1];
    my $token     = $_[2];

    # Ignore some tokens that we aren't interested in.
    return if not defined $token;       # Ignore undefs.
    return if $token eq '';             # Ignore blank cells.
    return if ref $token eq 'ARRAY';    # Ignore array refs.
    return if $token =~ /^=/;           # Ignore formula

    # Ignore numbers
    return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;

    # Ignore various internal and external hyperlinks. In a real scenario
    # you may wish to track the length of the optional strings used with
    # urls.
    return if $token =~ m{^[fh]tt?ps?://};
    return if $token =~ m{^mailto:};
    return if $token =~ m{^(?:in|ex)ternal:};

    # We store the string width as data in the Worksheet object. We use
    # a double underscore key name to avoid conflicts with future names.
    #
    my $old_width    = $worksheet->{__col_widths}->[$col];
    my $string_width = string_width($token);

    if ( not defined $old_width or $string_width > $old_width ) {

        # You may wish to set a minimum column width as follows.
        #return undef if $string_width < 10;

        $worksheet->{__col_widths}->[$col] = $string_width;
    }

    # Return control to write();
    return undef;
}

###############################################################################
#
# Very simple conversion between string length and string width for Arial 10.
# See below for a more sophisticated method.
#
sub string_width {

    return 0.9 * length $_[0];
}

###############################################################################
#
# This function uses an external module to get a more accurate width for a
# string. Note that in a real program you could "use" the module instead of
# "require"-ing it and you could make the Font object global to avoid repeated
# initialisation.
#
# Note also that the $pixel_width to $cell_width is specific to arial. For
# other fonts you should calculate appropriate relationships. A future verison
# of S::WE will provide a way of specifying column widths in pixels instead of
# cell units in order to simplify this conversion.
#
#sub string_width {
#
#    #require Font::TTFMetrics;
#
#    my $arial        = Font::TTFMetrics->new('c:\windows\fonts\arial.ttf');
#
#    my $font_size    = 10;
#    my $dpi          = 96;
#    my $units_per_em = $arial->get_units_per_em();
#    my $font_width   = $arial->string_width($_[0]);
#
#    # Convert to pixels as per TTFMetrics docs.
#    my $pixel_width  = 6 + $font_width *$font_size *$dpi /(72 *$units_per_em);
#
#    # Add extra pixels for border around text.
#    $pixel_width  += 6;
#
#    # Convert to cell width (for Arial) and for cell widths > 1.
#    my $cell_width   = ($pixel_width -5) /7;
#
#    return $cell_width;
#
#}

1;