#------------------------------------------------------------
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::Analytics::SnapshotUtils;

use strict;
use warnings;

use Data::Dumper;

use lib '/app/tools/common/lib';
use RSApache::Response::XSLT;
use Common::Assert;
use Common::XMLObject;
use Common::RSApp;
use Common::Log;
use Common::Country;

use lib '/app/tools/bookpub/lib';

use base 'BookPub::Analytics::BaseExport';

sub setFBold {

    my $self     = shift;
    my $workbook = shift;

    my $formatBold;
    $formatBold = $workbook->add_format();
    $formatBold->set_bold();

    return $formatBold;
}

sub setFBoldUnderline {

    my $self     = shift;
    my $workbook = shift;

    my $formatBoldUnderline;
    $formatBoldUnderline = $workbook->add_format();
    $formatBoldUnderline->set_bold();
    $formatBoldUnderline->set_underline();

    return $formatBoldUnderline;
}

sub setFCurrency {

    my $self     = shift;
    my $workbook = shift;

    # This needs to be fully internationalized sometime
    my $formatCurrency;
    $formatCurrency = $workbook->add_format();
    my $strVar = BookPub::Analytics::SnapshotUtils::getCurrencySymbol();
    $formatCurrency->set_num_format("$strVar#,##0.00");

    return $formatCurrency;
}

sub setFNumber {

    my $self     = shift;
    my $workbook = shift;

    my $formatWholeNumber;
    $formatWholeNumber = $workbook->add_format();
    $formatWholeNumber->set_num_format('#,##0');
    $formatWholeNumber->set_align('right');

    return $formatWholeNumber;
}

sub setFPercent {

    my $self     = shift;
    my $workbook = shift;

    my $formatPercent;
    $formatPercent = $workbook->add_format();
    $formatPercent->set_num_format('#0.0%');

    return $formatPercent;
}

sub setFItalics {
    my $self     = shift;
    my $workbook = shift;

    my $formatItalic;
    $formatItalic = $workbook->add_format();
    $formatItalic->set_italic();
    $formatItalic->set_size(8);

    return $formatItalic;
}

sub setFISBN13 {
    my $self     = shift;
    my $workbook = shift;

    my $formatISBN13;
    $formatISBN13 = $workbook->add_format();
    $formatISBN13->set_num_format('#');

    return $formatISBN13;
}

sub dateRangeFormat {
    my ($self)         = shift;
    my $periodType     = shift;
    my ($snapshotData) = shift;

    my $str;

    # "Jan 2011 (Q1)";
    if ( 'm' eq $periodType ) {
        if ($snapshotData->{xml}{EndPeriod}) {
            $str =
                $snapshotData->{xml}{Period}{StartDateDisp}{Month} . " "
              . $snapshotData->{xml}{Period}{StartDateDisp}{Year} . " - "
              . $snapshotData->{xml}{EndPeriod}{StartDateDisp}{Month} . " "
              . $snapshotData->{xml}{EndPeriod}{StartDateDisp}{Year};
        } else {
            $str =
                $snapshotData->{xml}{Period}{StartDateDisp}{Month} . " "
              . $snapshotData->{xml}{Period}{StartDateDisp}{Year} . " (Q"
              . $snapshotData->{xml}{Period}{Quarter} . ")";
        }
    }

    # "Q1 2011 (Jan-Mar)";
    elsif ( 'q' eq $periodType ) {
        $str = "Q"
          . $snapshotData->{xml}{Period}{Quarter} . " "
          . $snapshotData->{xml}{Period}{StartDateDisp}{Year} . " ("
          . $snapshotData->{xml}{Period}{StartDateDisp}{Month} . "-"
          . $snapshotData->{xml}{Period}{EndDateDisp}{Month} . ")";
    }

    # "2010 (Jan-Dec)";
    elsif ( 'y' eq $periodType ) {
        $str =
            $snapshotData->{xml}{Period}{StartDateDisp}{Year} . " ("
          . $snapshotData->{xml}{Period}{StartDateDisp}{Month} . "-"
          . $snapshotData->{xml}{Period}{EndDateDisp}{Month} . ")";
    }
    return $str;

}

sub getCurrencySymbol {
    my $currencySymbol = Common::Client::Current()->Locale()->currencyFormat()->symbol();
    return $currencySymbol;
}

# if RS ever moves to reporting revenue in more currencies than
# GBP and USD, this will have to be formally localized
#
sub stripper {
    my $str = shift;

    # if value is a percent, strip the % and return the value divided
    # by 100
    if ( $str =~ m/\%/ ) {
        $str =~ s/\%//g;
        return $str / 100;
    }

    # strip $ and , and £ from string
    $str =~ s/\x{000A3}//;     # £
    $str =~ s/\x{00024}//;     # $
    $str =~ s/\x{0002C}//g;    # , multiple ones

    return $str;
}

sub topServicesTitle { 'Analytics Snapshot - Analytics Home - Top Services' }
sub topFormatsTitle  { 'Analytics Snapshot - Analytics Home - Top Formats' }
sub topBooksTitle    { 'Analytics Snapshot - Analytics Home - Top Books' }
sub topAuthorsTitle  { 'Analytics Snapshot - Analytics Home - Top Authors' }

sub authorsSummaryTitle        { 'Analytics Snapshot - Authors Summary' }
sub authorDetailByServiceTitle { 'Analytics Snapshot - Author Detail by Service' }
sub authorDetailByFormatTitle  { 'Analytics Snapshot - Author Detail by Format' }

sub authorsSummarySheetName        { 'Authors Summary' }
sub authorDetailByServiceSheetName { 'Author Detail by Service' }
sub authorDetailByFormatSheetName  { 'Author Detail by Format' }

sub topServicesSheetName { 'Top Services' }
sub topFormatsSheetName  { 'Top Formats' }
sub topBooksSheetName    { 'Top Books' }
sub topAuthorsSheetName  { 'Top Authors' }

sub bookDetailByServiceSheetName   { 'Book Detail by Service' }
sub bookDetailByFormatSheetName    { 'Book Detail by Format' }
sub bookDetailByCountriesSheetName { 'Book Detail by Country' }
sub bookDetailByServiceTitle       { 'Analytics Snapshot - Book Detail by Service' }
sub bookDetailByFormatTitle        { 'Analytics Snapshot - Book Detail by Format' }
sub bookDetailByCountriesTitle     { 'Analytics Snapshot - Book Detail by Country' }

sub booksSummarySheetName { 'Top Books' }
sub booksSummaryTitle     { 'Analytics Snapshot - Top Books' }

sub byServiceSheetName { 'By Service' }
sub byFormatSheetName  { 'By Format' }

sub formatsSummarySheetName       { 'Top Formats' }
sub formatsSummaryTitle           { 'Analytics Snapshot - Formats Summary - Top Formats' }
sub topBooksFormatsSummaryTitle   { 'Analytics Snapshot - Formats Summary - Top Books' }
sub topAuthorsFormatsSummaryTitle { 'Analytics Snapshot - Formats Summary - Top Authors' }

sub countriesSummarySheetName { 'Countries Summary' }
sub countriesSheetName        { 'Countries' }
sub countriesSummaryTitle     { 'Analytics Snapshot - Countries Summary' }
sub countriesTitle            { 'Analytics Snapshot - Countries' }
sub countriesSummaryHeader    { "Top Countries" }
sub countriesHeader           { "Country" }

sub imprintsSummarySheetName { 'Imprints Summary' }
sub imprintsSheetName        { 'Imprints' }
sub imprintsSummaryTitle     { 'Analytics Snapshot - Imprints Summary' }
sub imprintsTitle            { 'Analytics Snapshot - Imprints' }
sub imprintsSummaryHeader    { "Top Imprints" }
sub imprintsHeader           { "Imprint Name" }

sub serviceFormatSummaryDetail { 'Analytics Snapshot - Format Summary - Service Format Detail' }

sub topBooksServiceFormatTitle   { 'Analytics Snapshot - Service Format Detail - Top Books' }
sub topAuthorsServiceFormatTitle { 'Analytics Snapshot - Service Format Detail - Top Authors' }

sub servicesSummarySheetName { 'Services Summary' }

sub servicesSummaryTitle           { 'Analytics Snapshot - Services Summary-  Top Services' }
sub topBooksServicesSummaryTitle   { 'Analytics Snapshot - Services Summary - Top Books' }
sub topAuthorsServicesSummaryTitle { 'Analytics Snapshot - Services Summary - Top Authors' }

sub topServicesFormatDetailTitle { 'Analytics Snapshot - Format Detail - Top Services' }
sub topBooksFormatDetailTitle    { 'Analytics Snapshot - Format Detail - Top Books' }
sub topAuthorsFormatDetailTitle  { 'Analytics Snapshot - Format Detail - Top Authors' }

sub topFormatsServiceDetailTitle { 'Analytics Snapshot - Service Detail - Top Formats' }
sub topBooksServiceDetailTitle   { 'Analytics Snapshot - Service Detail - Top Books' }
sub topAuthorsServiceDetailTitle { 'Analytics Snapshot - Service Detail - Top Authors' }

sub allPublishersLabel { 'All Publishers' }
sub allImprintsLabel   { 'All Imprints' }
sub allCountriesLabel  { 'All Countries' }
sub totalRevenueLabel  { 'Total Revenue' }
sub totalUnitsLabel    { 'Total Units' }
sub topServicesHeader  { 'Top Services' }
sub topFormatsHeader   { 'Top Formats' }
sub topBooksHeader     { 'Top Books' }
sub topAuthorsHeader   { 'Top Authors' }
sub shareHeader        { 'Share' }
sub revenueHeader      { 'Revenue' }
sub rankHeader         { 'Rank' }
sub lastRankHeader     { 'Last Rank' }
sub titleHeader        { 'Title' }
sub authorHeader       { 'Author' }
sub unitsHeader        { 'Units' }
sub serviceHeader      { 'Service' }
sub formatHeader       { 'Format' }
sub subTitleHeader     { 'Sub Title' }
sub ISBN10Header       { 'ISBN10' }
sub ISBN13Header       { 'ISBN13' }

sub unspecifiedLabel { '[unspecified]' }

sub disclaimer {
'Please note that all analytics data is subject to change and is not meant to function as a substitution for reconciled financial reporting.';
}

##############################################################################
#
# 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 + 1 ) 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       = shift;
    my $token     = shift;

    # 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 our disclaimer
    return if $token =~ BookPub::Analytics::SnapshotUtils::disclaimer();

    # 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) + 3;

    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;    # Play nicely.
###