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

use strict;
use warnings;

use Spreadsheet::WriteExcel;
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 BookPub::Analytics::ReportLib;
use BookPub::Analytics::BookDetailReport;
use BookPub::Analytics::SnapshotUtils;

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

my $snapshotData;

sub _init {
    my ( $self, %args ) = @_;

    $self->SUPER::_init(%args);

    $self->{data} = $self->_exportToVariable(%args);

    return $self;

}

sub _exportToVariable {
    my ( $self, %args ) = @_;

    my $tmp;
    my $col = 0;

    # We don't want to display Last Rank for custom periods.
    my $displayLastRank = 1;

    if ( $args{endPeriodParam} ) {
        $displayLastRank = 0;
    }

    $snapshotData->{xml} = BookPub::Analytics::BookDetailReport->new(
        periodType     => $args{periodType},
        periodParam    => $args{periodParam},
        endPeriodParam => $args{endPeriodParam},
        imprintID      => $args{imprintID},
        publisherID    => $args{publisherID},
        countryCode    => $args{countryCode},
        productID      => $args{productID},
        commandObj     => $args{commandObj},
    );

    # 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 $formatBold     = self->BookPub::Analytics::SnapshotUtils::setFBold($workbook);
    my $formatCurrency = self->BookPub::Analytics::SnapshotUtils::setFCurrency($workbook);
    my $formatNumber   = self->BookPub::Analytics::SnapshotUtils::setFNumber($workbook);
    my $formatPercent  = self->BookPub::Analytics::SnapshotUtils::setFPercent($workbook);
    my $formatItalics  = self->BookPub::Analytics::SnapshotUtils::setFItalics($workbook);
    my $formatISBN13   = self->BookPub::Analytics::SnapshotUtils::setFISBN13($workbook);

    my $worksheet1 = $workbook->add_worksheet( BookPub::Analytics::SnapshotUtils::bookDetailByServiceSheetName() );

    ############################################
    ### FIRST SHEET 'Book Detail by Service' ###
    ############################################
    my $row = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::bookDetailByServiceTitle();
    $worksheet1->write( $row, 0, $tmp, $formatBold );
    $row += 2;

    $tmp = $self->BookPub::Analytics::SnapshotUtils::dateRangeFormat( $args{periodType}, $snapshotData );
    $worksheet1->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{publisherID} ) {
        if ( $args{publisherID} eq 0 ) {
            $tmp = BookPub::Analytics::SnapshotUtils::unspecifiedLabel();
        } else {
            $tmp = $snapshotData->{xml}{Publisher}{PublisherName};
        }
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allPublishersLabel();
    }
    $worksheet1->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{imprintID} ) {
        $tmp = $snapshotData->{xml}{Imprint}{ImprintName};
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allImprintsLabel();
    }
    $worksheet1->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{countryCode} ) {
        $tmp = $snapshotData->{xml}{Country};
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allCountriesLabel();
    }
    $worksheet1->write( $row, 0, $tmp );
    $row += 2;

    # Product Info
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::titleHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{Title};
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::subTitleHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{SubTitle};
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::authorHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{Authors};
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::ISBN10Header();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{ISBN10};
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::ISBN13Header();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{ISBN13};
    _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatISBN13 );
    $row += 2;

    # Totals
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::totalRevenueLabel();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::stripper( $snapshotData->{xml}{Totals}{Revenue} );
    _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatCurrency );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::totalUnitsLabel();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::stripper( $snapshotData->{xml}{Totals}{Units} );
    _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::rankHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{Totals}{Rank};
    _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );
    $row++;

    if ($displayLastRank) {
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::lastRankHeader();
        _write_worksheet( $worksheet1, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{Totals}{PrevRank};
        if ( $tmp eq 0 ) {
            $tmp = "--";
        }
        _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );
        $row += 2;
    } else {
        $row++;
    }

    # the "by service" rows...
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::rankHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );

    if ($displayLastRank) {
        $tmp = BookPub::Analytics::SnapshotUtils::lastRankHeader();
        _write_worksheet( $worksheet1, $row, $col++, $tmp );
    }

    $tmp = BookPub::Analytics::SnapshotUtils::serviceHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::shareHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::unitsHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::revenueHeader();
    _write_worksheet( $worksheet1, $row, $col++, $tmp );
    $row++;

    my %bigHash;

    # First, get a hashref to the Tables
    # All the data we need is in the table, except the Share, which we need
    # to get from the Pies.  That's why we put all the data in bigHash
    my $hashref = $snapshotData->{xml}{Tables};
    while ( my ( $key, $value ) = each(%$hashref) ) {

        # there's several values in the hash and they're an array (of hashes)
        my @tableArray = @$value;

        # walk the array until we find the one
        foreach my $tableHashRef (@tableArray) {
            my %hash = %$tableHashRef;
            if ( $hash{TableName} eq "book_revenue_by_service" ) {
                my $arrayRef = $hash{DataRow};
                foreach my $cell (@$arrayRef) {
                    my ( $key2, $value2 ) = %$cell;
                    my $svcName;
                    foreach my $item (@$value2) {
                        if ( $item->{Key} eq "ProductInfo" ) {
                            $svcName = $item->{ProductInfo}{ServiceName};
                            $bigHash{$svcName}{Service} = $svcName;
                        } elsif ( $item->{Key} eq "Rank" ) {
                            if ( defined( $item->{Value} ) ) {
                                $bigHash{$svcName}{Rank} = $item->{Value};
                            } else {
                                $bigHash{$svcName}{Rank} = "--";
                            }
                        } elsif ( $item->{Key} eq "PrevRank" && $displayLastRank ) {
                            if ( defined( $item->{Value} ) ) {
                                $bigHash{$svcName}{PrevRank} = $item->{Value};
                            } else {
                                $bigHash{$svcName}{PrevRank} = "--";
                            }
                        } elsif ( $item->{Key} eq "Units" ) {
                            $bigHash{$svcName}{Units} = $item->{Value};
                        } elsif ( $item->{Key} eq "Revenue" ) {
                            $bigHash{$svcName}{Revenue} = $item->{Value};

                            #strip $ and , for sorting
                            my $raw = $item->{Value};
                            $raw =~ s/[^0-9.]+//g;
                            $bigHash{$svcName}{RawRevenue} = $raw;
                        }    #ifelse
                    }    #foreach
                }    #foreach
            }    #if
        }    #foreach
    }    #while
         # Second, get a hashref to the Pies
    $hashref = $snapshotData->{xml}{Pies};
    while ( my ( $key, $value ) = each(%$hashref) ) {

        # there's only one value in the hash and it's an array (of hashes)
        my @pieArray = @$value;

        # walk the array until we find the one
        foreach my $pieHashRef (@pieArray) {
            if ( $pieHashRef->{PieName} eq "top_services" ) {

                # get a hashref to the Legend
                my $legendRef = $pieHashRef->{Legend};
                while ( my ( $key2, $value2 ) = each(%$legendRef) ) {

                    # get the array of hashes in it
                    my @legendArray = @$value2;

                    # walk the array getting service and percentfrom it
                    foreach my $itemHashRef (@legendArray) {
                        my %itemHash = %$itemHashRef;
                        $bigHash{ $itemHash{Label} }{Percent} = $itemHash{Percent};
                    }    #foreach
                }    #while
            }    #if
        }    #foreach
    }    #while
         # numerical sort by RawRevenue
    foreach my $key (
        sort { $bigHash{$b}->{RawRevenue} <=> $bigHash{$a}->{RawRevenue} }
        keys %bigHash
      ) {
        my $col   = 0;
        my $value = $bigHash{$key};
        $tmp = $value->{Rank};
        _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );

        if ($displayLastRank) {
            $tmp = $value->{PrevRank};
            _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );
        }
        $tmp = $key;
        _write_worksheet( $worksheet1, $row, $col++, $tmp );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Percent} );
        _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatPercent );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Units} );
        _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatNumber );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Revenue} );
        _write_worksheet( $worksheet1, $row, $col++, $tmp, $formatCurrency );
        $row++;
    }
    $row += 3;

    $tmp = BookPub::Analytics::SnapshotUtils::disclaimer();
    $worksheet1->write( $row, 0, $tmp, $formatItalics );

    BookPub::Analytics::SnapshotUtils::autofit_columns($worksheet1);


    ############################################
    ### SECOND SHEET 'Book Detail by Format' ###
    ############################################
    my $worksheet2 = $workbook->add_worksheet( BookPub::Analytics::SnapshotUtils::bookDetailByFormatSheetName() );
    $row = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::bookDetailByFormatTitle();
    $worksheet2->write( $row, 0, $tmp, $formatBold );
    $row += 2;

    $tmp = $self->BookPub::Analytics::SnapshotUtils::dateRangeFormat(%args);
    $worksheet2->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{publisherID} ) {
        if ( $args{publisherID} eq 0 ) {
            $tmp = BookPub::Analytics::SnapshotUtils::unspecifiedLabel();
        } else {
            $tmp = $snapshotData->{xml}{Publisher}{PublisherName};
        }
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allPublishersLabel();
    }
    $worksheet2->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{imprintID} ) {
        $tmp = $snapshotData->{xml}{Imprint}{ImprintName};
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allImprintsLabel();
    }
    $worksheet2->write( $row, 0, $tmp );
    $row++;

    if ( defined $args{countryCode} ) {
        $tmp = $snapshotData->{xml}{Country};
    } else {
        $tmp = BookPub::Analytics::SnapshotUtils::allCountriesLabel();
    }
    $worksheet2->write( $row, 0, $tmp );
    $row += 2;

    # Product Info
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::titleHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{Title};
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::subTitleHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{SubTitle};
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::authorHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{Authors};
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::ISBN10Header();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{ISBN10};
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::ISBN13Header();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{ProductInfo}{ISBN13};
    _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatISBN13 );
    $row += 2;

    # Totals
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::totalRevenueLabel();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::stripper( $snapshotData->{xml}{Totals}{Revenue} );
    _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatCurrency );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::totalUnitsLabel();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::stripper( $snapshotData->{xml}{Totals}{Units} );
    _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );
    $row++;

    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::rankHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = $snapshotData->{xml}{Totals}{Rank};
    _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );
    $row++;

    if ($displayLastRank) {
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::lastRankHeader();
        _write_worksheet( $worksheet2, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{Totals}{PrevRank};
        if ( $tmp eq 0 ) {
            $tmp = "--";
        }
        _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );
        $row += 2;
    } else {
        $row++;
    }

    # the "by format" rows...
    $col = 0;
    $tmp = BookPub::Analytics::SnapshotUtils::rankHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );

    if ($displayLastRank) {
        $tmp = BookPub::Analytics::SnapshotUtils::lastRankHeader();
        _write_worksheet( $worksheet2, $row, $col++, $tmp );
    }

    $tmp = BookPub::Analytics::SnapshotUtils::formatHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::shareHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::unitsHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $tmp = BookPub::Analytics::SnapshotUtils::revenueHeader();
    _write_worksheet( $worksheet2, $row, $col++, $tmp );
    $row++;

    %bigHash = ();

    # First, get a hashref to the Tables
    $hashref = $snapshotData->{xml}{Tables};
    while ( my ( $key, $value ) = each(%$hashref) ) {

        # there's several values in the hash and they're an array (of hashes)
        my @tableArray = @$value;

        # walk the array until we find the one
        foreach my $tableHashRef (@tableArray) {
            my %hash = %$tableHashRef;
            if ( $hash{TableName} eq "book_revenue_by_format" ) {
                my $arrayRef = $hash{DataRow};
                foreach my $cell (@$arrayRef) {
                    my ( $key2, $value2 ) = %$cell;
                    my $fmtName;
                    foreach my $item (@$value2) {
                        if ( $item->{Key} eq "ProductInfo" ) {
                            $fmtName = $item->{ProductInfo}{FormatName};
                            $bigHash{$fmtName}{Format} = $fmtName;
                        } elsif ( $item->{Key} eq "Rank" ) {
                            if ( defined( $item->{Value} ) ) {
                                $bigHash{$fmtName}{Rank} = $item->{Value};
                            } else {
                                $bigHash{$fmtName}{Rank} = "--";
                            }
                        } elsif ( $item->{Key} eq "PrevRank" && $displayLastRank ) {
                            if ( defined( $item->{Value} ) ) {
                                $bigHash{$fmtName}{PrevRank} = $item->{Value};
                            } else {
                                $bigHash{$fmtName}{PrevRank} = "--";
                            }
                        } elsif ( $item->{Key} eq "Units" ) {
                            $bigHash{$fmtName}{Units} = $item->{Value};
                        } elsif ( $item->{Key} eq "Revenue" ) {
                            $bigHash{$fmtName}{Revenue} = $item->{Value};

                            #strip $ and , for sorting
                            my $raw = $item->{Value};
                            $raw =~ s/[^0-9.]+//g;
                            $bigHash{$fmtName}{RawRevenue} = $raw;
                        }    #ifelse
                    }    #foreach
                }    #foreach
            }    #if
        }    #foreach
    }    #while
         # Second, get a hashref to the Pies
    $hashref = $snapshotData->{xml}{Pies};
    while ( my ( $key, $value ) = each(%$hashref) ) {

        # there's only one value in the hash and it's an array (of hashes)
        my @pieArray = @$value;

        # walk the array until we find the one
        foreach my $pieHashRef (@pieArray) {
            if ( $pieHashRef->{PieName} eq "top_formats" ) {

                # get a hashref to the Legend
                my $legendRef = $pieHashRef->{Legend};
                while ( my ( $key2, $value2 ) = each(%$legendRef) ) {

                    # get the array of hashes in it
                    my @legendArray = @$value2;

                    # walk the array getting percent from it
                    foreach my $itemHashRef (@legendArray) {
                        my %itemHash = %$itemHashRef;
                        $bigHash{ $itemHash{Label} }{Percent} = $itemHash{Percent};
                    }    #foreach
                }    #while
            }    #if
        }    #foreach
    }    #while
         # numerical sort by RawRevenue
    foreach my $key (
        sort { $bigHash{$b}->{RawRevenue} <=> $bigHash{$a}->{RawRevenue} }
        keys %bigHash
      ) {
        my $col   = 0;
        my $value = $bigHash{$key};
        $tmp = $value->{Rank};
        _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );

        if ($displayLastRank) {
            $tmp = $value->{PrevRank};
            _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );
        }

        $tmp = BookPub::Analytics::SnapshotUtils::stripper($key);
        _write_worksheet( $worksheet2, $row, $col++, $tmp );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Percent} );
        _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatPercent );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Units} );
        _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatNumber );
        $tmp = BookPub::Analytics::SnapshotUtils::stripper( $value->{Revenue} );
        _write_worksheet( $worksheet2, $row, $col++, $tmp, $formatCurrency );
        $row++;
    }
    $row += 3;

    $tmp = BookPub::Analytics::SnapshotUtils::disclaimer();
    $worksheet2->write( $row, 0, $tmp, $formatItalics );

    BookPub::Analytics::SnapshotUtils::autofit_columns($worksheet2);

    ##############################################
    ### THIRD SHEET 'Book Detail by Countries' ###
    ##############################################
    if ( !$args{countryCode} ) {
        my $worksheet3 = $workbook->add_worksheet( BookPub::Analytics::SnapshotUtils::bookDetailByCountriesSheetName() );

        $row = 0;

        $tmp = BookPub::Analytics::SnapshotUtils::bookDetailByCountriesTitle();
        $worksheet3->write( $row, 0, $tmp, $formatBold );
        $row += 2;

        ### TITLES ###
        # Period Date
        $tmp = $self->BookPub::Analytics::SnapshotUtils::dateRangeFormat( $args{periodType}, $snapshotData );
        $worksheet3->write( $row, 0, $tmp );
        $row++;

        # Publisher
        if ( defined $args{publisherID} ) {
            if ( $args{publisherID} eq 0 ) {
                $tmp = BookPub::Analytics::SnapshotUtils::unspecifiedLabel();
            } else {
                $tmp = $snapshotData->{xml}{Publisher}{PublisherName};
            }
        } else {
            $tmp = BookPub::Analytics::SnapshotUtils::allPublishersLabel();
        }
        $worksheet3->write( $row, 0, $tmp );
        $row++;

        # Imprint
        if ( defined $args{imprintID} ) {
            $tmp = $snapshotData->{xml}{Imprint}{ImprintName};
        } else {
            $tmp = BookPub::Analytics::SnapshotUtils::allImprintsLabel();
        }
        $worksheet3->write( $row, 0, $tmp );
        $row += 2;

        # Title
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::titleHeader();
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{ProductInfo}{Title};
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $row++;

        # SubTitle
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::subTitleHeader();
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{ProductInfo}{SubTitle};
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $row++;

        # Author
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::authorHeader();
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{ProductInfo}{Authors};
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $row++;

        # ISBN10
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::ISBN10Header();
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{ProductInfo}{ISBN10};
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $row++;

        # ISBN13
        $col = 0;
        $tmp = BookPub::Analytics::SnapshotUtils::ISBN13Header();
        _write_worksheet( $worksheet3, $row, $col++, $tmp );
        $tmp = $snapshotData->{xml}{ProductInfo}{ISBN13};
        _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatISBN13 );
        $row += 2;

        ## DATA TABLE SECTION ##
        $hashref = $snapshotData->{xml}{Tables};
        while ( my ( $key, $value ) = each(%$hashref) ) {

            # there's several values in the hash and they're an array (of hashes)
            my @tableArray = @$value;

            # walk the array until we find the one
            foreach my $tableHashRef (@tableArray) {
                my %hash = %$tableHashRef;
                next if $hash{TableName} ne "book_revenue_by_country";

                my $headerArrayRef = $hash{HeaderRow};
                foreach my $cell (@$headerArrayRef) {
                    my ( $key2, $value2 ) = %$cell;
                    my $col = 0;
                    foreach my $item (@$value2) {
                        if ( $item->{Key} eq "ProductInfo" ) {
                            $tmp = "Country";
                        } elsif ( $item->{Key} eq "PrevRank" ) {
                            $tmp = "Last Rank";
                        } else {
                            $tmp = $item->{Value};
                        }
                        _write_worksheet( $worksheet3, $row, $col++, $tmp );
                    }

                }
                $row++;

                my $arrayRef = $hash{DataRow};
                foreach my $cell (@$arrayRef) {
                    my ( $key2, $value2 ) = %$cell;
                    my $col = 0;
                    foreach my $item (@$value2) {
                        if ( $item->{Key} eq "Rank" ) {
                            $tmp = $item->{Value};
                            _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatNumber );
                        } elsif ( $item->{Key} eq "PrevRank" && $displayLastRank ) {
                            if ( length( $item->{Value} ) eq 0 ) {
                                $tmp = "--";
                            } else {
                                $tmp = $item->{Value};
                            }
                            _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatNumber );
                        } elsif ( $item->{Key} eq "ProductInfo" ) {
                            $tmp = $item->{ProductInfo}{CountryName};
                            _write_worksheet( $worksheet3, $row, $col++, $tmp );
                        } elsif ( $item->{Key} eq "Units" ) {
                            $tmp = BookPub::Analytics::SnapshotUtils::stripper( $item->{Value} );
                            _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatNumber );
                        } elsif ( $item->{Key} eq "Revenue" ) {
                            $tmp = BookPub::Analytics::SnapshotUtils::stripper( $item->{Value} );
                            _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatCurrency );
                        } elsif ( $item->{Key} eq "Data" ) {
                            $tmp = BookPub::Analytics::SnapshotUtils::stripper( $item->{Value} );
                            _write_worksheet( $worksheet3, $row, $col++, $tmp, $formatCurrency );
                        }
                    }

                    $row++;
                }

            }

        }
        $row += 3;

        $tmp = BookPub::Analytics::SnapshotUtils::disclaimer();
        $worksheet3->write( $row, 0, $tmp, $formatItalics );

        BookPub::Analytics::SnapshotUtils::autofit_columns($worksheet3);
    }

    $workbook->close();

    return $str;
}

###############################################################################
#
#
sub _write_worksheet {

    my $worksheet = shift;
    my $row       = shift;
    my $col       = shift;
    my $string    = shift;
    my $format    = shift;

    BookPub::Analytics::SnapshotUtils::_store_string_widths( $worksheet, $col, $string );
    $worksheet->write( $row, $col, $string, $format );
}

###
1;    # Play nicely.
###