package BookPub::Sale::Report::Royalty::HachetteUK;

use strict;
use Template;
use Data::Dumper;
use Text::CSV;

use lib '/app/tools/common/lib';
use Common::Log;
use Common::Client;
use Common::Email;
use Common::DB::ItemCollection;
use Common::DB::Item;
use Common::Util qw(formatFixedPoint);

use lib '/app/tools/bookpub/lib';
use BookPub::DB::Item::BookProduct;
use BookPub::DB::Item::Period;
use BookPub::DB::Item::Sale;
use BookPub::DB::Item::File;
use BookPub::DB::Item::Imprint;
use BookPub::DB::Item::Publisher;
use BookPub::DB::Item::Service;
use BookPub::Tracker::Service;

use base 'BookPub::Sale::Report::Royalty';

use constant kDB         => Common::DB::Item::kClientDB;
use constant OUTDIR_BASE => '/app/data/sale_report/tmp/';

sub new {
    my $class = shift;

    my $self = {};
    bless $self, $class;

    $self->_init(@_);

    return $self;
}

sub _baseDir {
    my ($self) = @_;

    if ( !$self->{_baseDir} ) {
        my $config = BookPub::Config->new();
        my $client = Common::Client::Current();

        $self->{_baseDir} = $config->get('sale_report_dir');
        $self->{_baseDir} .= '/' . $client->ClientNameClean();
        $self->{_baseDir} .= '/' . $self->{_periodID};
    }
    return $self->{_baseDir};
}

# _getFileName - generates a filename of the following form:
#     XXXXX_PeriodName_YYYYMMDD.csv
# where
#    XXXXX is a basename prefix
#    PeriodName is the name of the period
#    YYYYMMDD is the date the period was closed
#
sub _getFileName {
    my $self     = shift;
    my $baseName = shift;
    my $suffix   = shift;

    my $periodInfo;
    if ( !$self->{_periodInfo} ) {
        my $period = BookPub::DB::Item::Period->Lookup( period_id => $self->{_periodID} );

        my $periodName = $period->name;
        $periodName =~ s/\s+/_/g;
        $periodName =~ s/\W+//g;
        $periodName =~ s/__/_/g;

        my $endDate = $period->end_date;
        $endDate =~ s/-//g;

        $self->{_periodInfo} = $periodName . "_" . $endDate;
    }
    $periodInfo = $self->{_periodInfo};

    return ( $baseName . $periodInfo . "." . $suffix );
}

sub fileNameStd {
    my ($self) = @_;
    return $self->_getFileName( "Standard_", "csv" );
}

sub fileNameHHC {
    return shift->_getFileName( "HHC_", "csv" );
}

sub fileNameBookpoint {
    return shift->_getFileName( "Bookpoint_", "csv" );
}

sub fileNameLBS {
    return shift->_getFileName( "LBS_", "csv" );
}

sub fileNameOther {
    return shift->_getFileName( "Other_", "csv" );
}

sub fileNameSupplemental {
    return shift->_getFileName( "Supplemental_", "csv" );
}

sub filePath {
    my ($self) = @_;
    if ( !$self->{_filePath} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePath} = $baseDir . '/' . $self->fileNameStd();
    }
    return $self->{_filePath};
}

sub filePathHHC {
    my ($self) = @_;
    if ( !$self->{_filePathHHC} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathHHC} = $baseDir . '/' . $self->fileNameHHC();
    }
    return $self->{_filePathHHC};
}

sub filePathBookpoint {
    my ($self) = @_;
    if ( !$self->{_filePathBookpoint} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathBookpoint} = $baseDir . '/' . $self->fileNameBookpoint();
    }
    return $self->{_filePathBookpoint};
}

sub filePathLBS {
    my ($self) = @_;
    if ( !$self->{_filePathLBS} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathLBS} = $baseDir . '/' . $self->fileNameLBS();
    }
    return $self->{_filePathLBS};
}

sub filePathOther {
    my ($self) = @_;
    if ( !$self->{_filePathOther} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathOther} = $baseDir . '/' . $self->fileNameOther();
    }
    return $self->{_filePathOther};
}

sub filePathSupplemental {
    my ($self) = @_;
    if ( !$self->{_filePathSupplemental} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathSupplemental} = $baseDir . '/' . $self->fileNameSupplemental();
    }
    return $self->{_filePathSupplemental};
}

sub zipFileName {
    my ($self) = @_;
    if ( !$self->{_zipFileName} ) {
        my $name = $self->_getFileName( "", "zip" );

        $self->{_zipFileName} = $name;
    }
    return $self->{_zipFileName};
}

sub _init {
    my $self = shift;
    my %args = @_;

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

    $self->{clientID} = $args{clientID} ? $args{clientID} : Common::RSApp::GetClientID();
    $self->{tmplObj} = Template->new( { START_TAG => '{', END_TAG => '}' } );

}

sub _isHHC {
    my $self        = shift;
    my $publisherID = shift;

    # HHC
    #   Hachette Books Ireland (id=13)
    #   Hachette Children's Books (id=7)
    #   Hachette Children's Group (id=23)
    #   Headline (id=3)
    #   Hodder & Stoughton (id=2)
    #   Hodder Education (id=12)
    #   Jessica Kingsley Publishers (id=41)
    #   John Murray (id=8)
    #   John Murray Press (id=42)
    #   Kyle Cathie Ltd (id=37)
    #   Little, Brown Book Group (id=6)
    #   Octopus (id=5)
    #   Orion (id=1)
    #   Quercus Publishing (id=22)
    #   Quercus (id=24)
    #   Short Books(id=44)
    #   New River (id=56)
    #   Dialogue (id=57)
    #   Summersdale (id=58)
    #   Hachette Learning (id=65)
    # Note: these IDs and names are for HachetteUK
    return ( $publisherID =~ /^(7|23|13|3|2|12|8|22|24|6|5|1|37|41|42|44|56|57|58|65)$/ ) ? 1 : undef;
}

sub _isOther {
    my $self        = shift;
    my $publisherID = shift;

    # Other (International)
    #   Affirm Press (id=36)
    #   Bookouture (id=38)
    #   Hachette Australia (id=4)
    #   Hachette India (id=14)
    #   Hachette New Zealand (id=9)
    #   Magabala Books (id=68)
    # Note: these IDs and names are for HachetteUK
    return ( $publisherID =~ /^(4|14|9|36|38|68)$/ ) ? 1 : undef;
}

sub _includesTax {
    my $self        = shift;
    my $priceTypeID = shift;

    #   RRP + Tax (id=2)
    #   Agency + Tax (id=42)
    return ( $priceTypeID =~ /^(2|42)$/ ) ? 1 : undef;
}

# _generateReport - creates publisher group reports
#
sub _generateReport {
    my ($self, $csv, $fp, $report, $hPurchaseTypeMap) = @_;

    # The publisher group report hash must have the following structure:
    #
    #  $report{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
    #      {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
    #      {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
    #      {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}
    #      {$saleConversionRate}{revenue}
    #
    #  $report{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
    #      {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
    #      {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
    #      {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}
    #      {$saleConversionRate}{units}

    foreach my $fileID ( sort keys %$report ) {
        my $fileSales = $report->{$fileID};

        foreach my $transactionType ( sort { $b cmp $a } keys %$fileSales ) {
            my $transactionSales = $fileSales->{$transactionType};

            foreach my $isbn13 ( sort keys %$transactionSales ) {
                my $isbn13Sales = $transactionSales->{$isbn13};

                foreach my $countryCode ( sort keys %$isbn13Sales ) {
                    my $countrySales = $isbn13Sales->{$countryCode};

                    foreach my $serviceName ( sort keys %$countrySales ) {
                        my $serviceSales = $countrySales->{$serviceName};

                        foreach my $saleDate ( sort keys %$serviceSales ) {
                            my $saleDateSales = $serviceSales->{$saleDate};

                            foreach my $title ( sort keys %$saleDateSales ) {
                                my $titleSales = $saleDateSales->{$title};

                                foreach my $author ( sort keys %$titleSales ) {
                                    my $authorSales = $titleSales->{$author};

                                    foreach my $publisher ( sort keys %$authorSales ) {
                                        my $publisherSales = $authorSales->{$publisher};

                                        foreach my $imprint ( sort keys %$publisherSales ) {
                                            my $imprintSales = $publisherSales->{$imprint};

                                            foreach my $publisherPrice ( sort keys %$imprintSales ) {
                                                my $publisherPriceSales = $imprintSales->{$publisherPrice};

                                                foreach my $listPriceCurrency ( sort keys %$publisherPriceSales ) {
                                                    my $listPriceCurrencySales = $publisherPriceSales->{$listPriceCurrency};

                                                    foreach my $pubListPrice ( sort keys %$listPriceCurrencySales ) {
                                                        my $pubListPriceSales = $listPriceCurrencySales->{$pubListPrice};

                                                        foreach my $retailerSellingPrice ( sort keys %$pubListPriceSales ) {
                                                            my $retailerSellingPriceSales = $pubListPriceSales->{$retailerSellingPrice};

                                                            foreach my $retailerSellingPriceCurrency ( sort keys %$retailerSellingPriceSales ) {
                                                                my $retailerSellingPriceCurrencySales = $retailerSellingPriceSales->{$retailerSellingPriceCurrency};

                                                                foreach my $discount ( sort keys %$retailerSellingPriceCurrencySales ) {
                                                                    my $discountSales = $retailerSellingPriceCurrencySales->{$discount};

                                                                    foreach my $discountAmount ( sort keys %$discountSales ) {
                                                                        my $discountAmountSales = $discountSales->{$discountAmount};

                                                                        foreach my $currencyCode ( sort keys %$discountAmountSales ) {
                                                                            my $currencyCodeSales = $discountAmountSales->{$currencyCode};

                                                                            foreach my $altConversionCurrency ( sort keys %$currencyCodeSales ) {
                                                                                my $altConversionCurrencySales = $currencyCodeSales->{$altConversionCurrency};

                                                                                foreach my $altConversionRate ( sort keys %$altConversionCurrencySales ) {
                                                                                    my $altConversionRateSales = $altConversionCurrencySales->{$altConversionRate};

                                                                                    foreach my $saleConversionRate ( sort keys %$altConversionRateSales ) {
                                                                                        my $purchaseTypes = $altConversionRateSales->{$saleConversionRate};

                                                                                        foreach my $purchaseType ( sort keys %$purchaseTypes ) {
                                                                                            my $rPurchaseTypes = $purchaseTypes->{$purchaseType};

                                                                                            foreach my $rPurchaseType ( sort keys %$rPurchaseTypes ) {
                                                                                                my $row = $rPurchaseTypes->{$rPurchaseType};

                                                                                                my $units         = $row->{units};
                                                                                                my $revenueAmount = $row->{revenue};

                                                                                                my $gbpAmount = formatFixedPoint(
                                                                                                    $revenueAmount * $saleConversionRate, 2 );

                                                                                                my $convertedPayment = $altConversionRate
                                                                                                    ? formatFixedPoint($revenueAmount * $altConversionRate, 2 )
                                                                                                    : '';

                                                                        # We don't want to show blank converted payment or alt conversion currency
                                                                        # info.  If we don't have a converted payment, then just show the file's
                                                                        # reported currency (FB5373).
                                                                        #
                                                                                                my $_convertedPayment;
                                                                                                my $_paymentCurrency;

                                                                                                if ( '' eq $convertedPayment ) {
                                                                                                    $_convertedPayment = $revenueAmount;
                                                                                                    $_paymentCurrency  = $currencyCode;
                                                                                                } else {
                                                                                                    $_convertedPayment = $convertedPayment;
                                                                                                    $_paymentCurrency  = $altConversionCurrency;
                                                                                                }

                                                                                                $_convertedPayment =
                                                                                                formatFixedPoint( $_convertedPayment, 2 );

                                                                                                $purchaseType = $hPurchaseTypeMap->{$purchaseType} if exists $hPurchaseTypeMap->{$purchaseType};

                                                                                                # output the data !!!
                                                                                                my @outputRow = (
                                                                                                    $saleDate,           # A - Sale Period
                                                                                                    $serviceName,        # B - Retailer
                                                                                                    $isbn13,             # C - ISBN 13
                                                                                                    $title,              # D - Title
                                                                                                    $author,             # E - Author
                                                                                                    $publisher,          # F - Division
                                                                                                    $imprint,            # G - Imprint
                                                                                                    $units,              # H - Net Units
                                                                                                    $countryCode,        # I - Territory Code
                                                                                                    $_convertedPayment,  # J - Conversion to Payment Currency
                                                                                                    $_paymentCurrency,   # K - Payment Currency
                                                                                                    $gbpAmount,          # L - GBP Amount
                                                                                                    $purchaseType,       # M - purchaseType
                                                                                                    $rPurchaseType,      # N - rPurchaseType
                                                                                                );

                                                                                                #print $fp join(",", @outputRow) , "\n";
                                                                                                $csv->print( $fp, \@outputRow );
                                                                                                print $fp "\n";
                                                                                            }    #rPurchaseType
                                                                                        }    #purchaseType
                                                                                    }    #saleConversionRate
                                                                                }    #altConversionRate
                                                                            }    #altConversionCurrency
                                                                        }    #currencyCode
                                                                    }    #discountAmount
                                                                }    #discount
                                                            }    #retailerSellingPriceCurrency
                                                        }    #retailerSellingPrice
                                                    }    #pubListPrice
                                                }    #listPriceCurrency
                                            }    #publisherPrice
                                        }    #imprint
                                    }    #publisher
                                }    #author
                            }    #title
                        }    #saleDate
                    }    #serviceName
                }    #countryCode
            }    #isbn13
        }    #transactionType
    }    #fileID

}    #_generateReport

sub create {
    my $self = shift;

    my $csv = Text::CSV->new( { binary => 1 } );

    # We'll keep a separate tally of units and revenue and
    # return those at the end for validation.
    my $totalUnits;
    my $totalRevenueUnrounded;

    my $periodID = $self->periodID();

    my $sales = BookPub::DB::Item::Sale->GetFinalSalesByPeriod($periodID);

    my $period     = BookPub::DB::Item::Period->Lookup( period_id => $periodID );
    my $outputDate = $period->end_date;
    my $year       = substr( $outputDate, 2, 2 );
    my $month      = substr( $outputDate, 5, 2 );

    $outputDate = $year . $month;

    # Report hashes -- these will hold the aggregated sale information.  HUK will have a
    # standard report, along with up to four additional reports.
    #
    my %standardRows;    # Standard report, has everything
    my %hhcRows;         # Report data for the HHC publisher group (if any)
    my %otherRows;       # Report data for the Other (International) publisher group (if any)
    my %suppRows;        # Report data for sales not in the Bookpoint, LBS or Other publisher groups.

    # Gonna cache some things so that we don't have to look them up in the DB every time.
    my %titles;
    my %fileNames;
    my %fileDates;
    my %fileServiceIDs;
    my %altFileCurrency;
    my %imprintNames;
    my %publisherNames;
    my %serviceNames;

    # We want to file a few imprints under the same name later, so let's go ahead and put those in the hash now.
    my $parentImprintName = "Constable & Robinson";
    $imprintNames{92}  = $parentImprintName;    # C & R Crime
    $imprintNames{96}  = $parentImprintName;    # Canvas
    $imprintNames{91}  = $parentImprintName;    # Constable
                                                #$imprintNames{ }  = $parentImprintName; # Constable Crime
    $imprintNames{90}  = $parentImprintName;    # Corsair
    $imprintNames{94}  = $parentImprintName;    # How To Books
    $imprintNames{95}  = $parentImprintName;    # Magpie
    $imprintNames{100} = $parentImprintName;    # Mammoth
    $imprintNames{99}  = $parentImprintName;    # Much-in-Little
    $imprintNames{97}  = $parentImprintName;    # Right Way
    $imprintNames{89}  = $parentImprintName;    # Robinson
    $imprintNames{98}  = $parentImprintName;    # Spring Hill


    # Purcahse Types abbr to full name mapping
    my %purchaseTypeMap = (
        'phys' => 'Physical Retail',
        'down' => 'Download',
        'subd' => 'Subscription Download',
        'strm' => 'Stream',
        'pod ' => 'Print On Demand',
        'iapu' => 'In App Purchase',
        'loan' => 'Loan',
        'prmo' => 'Promotional',
    );

    # Loop over all the sales in the period.  We'll build up the report hashes as we go
    # along.
    #
    while ( my $sale = $sales->next() ) {
        my $saleID           = $sale->sale_id;
        my $fileID           = $sale->file_id;
        my $priceIncludesTax = $self->_includesTax( $sale->price_type_id );
        my $unitTax;
        if ( $sale->units ) {

            # We will be adding or subtracting this from the list price later,
            # so we only want the positive value.
            $unitTax = abs( $sale->tax_amount / $sale->units );
        }

        # 1) Sale Period
        #

        my $saleDate;
        my $fileServiceID;

        if ( $fileServiceIDs{$fileID} ) {
            $fileServiceID = $fileServiceIDs{$fileID};
        } else {
            my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );
            $fileServiceID = $file->service_id;
            $fileServiceIDs{$fileID} = $fileServiceID;
        }

        # Apple sale dates can sometimes span multiple months,
        # so we're just going to grab the date from the file name for them.
        if ( $fileServiceID == 9 ) {
            if ( !$fileDates{$fileID} ) {
                my $date = $self->_getDateFromAppleFileName($fileID);

                if ($date) {
                    $fileDates{$fileID} = $date;
                } else {
                    my $file     = BookPub::DB::Item::File->Lookup( file_id => $fileID );
                    my $fileName = $file->orig_file_name;
                    my $error    = "Unable to find date in " . $fileName;
                    $self->_sendErrorEmail($error);
                    die $error . "\n";
                }
            }
            $saleDate = $fileDates{$fileID};
        } else {
            $saleDate = $sale->date_end;
            my $saleDateYear = substr( $saleDate, 2, 2 );
            $saleDateYear = "20" . $saleDateYear if ( $saleDateYear =~ /^\d\d$/ );
            my $saleDateMonth = substr( $saleDate, 5, 2 );
            $saleDate = $saleDateMonth . "/" . $saleDateYear;
        }

        # 2) Find the Retailer
        #
        my $serviceName;
        my $serviceID = $sale->service_id;

        if ( $serviceNames{$serviceID} ) {
            $serviceName = $serviceNames{$serviceID};
        } else {
            my $service = BookPub::DB::Item::Service->Lookup( service_id => $serviceID );
            $serviceName = $service->service_name;
            $serviceNames{$serviceID} = $serviceName;
        }

        # 3) Get the ISBN13
        #
        my $productID   = $sale->product_id;
        my $bookProduct = BookPub::DB::Item::BookProduct->Lookup( product_id => $productID );
        my $isbn13      = $bookProduct->isbn13;

        # 4) Find the book title
        #
        my $title;
        my $bookID = $bookProduct->book_id;
        if ( $titles{$bookID} ) {
            $title = $titles{$bookID};
        } else {
            my $book = BookPub::DB::Item::Book->Lookup( book_id => $bookID );
            $title = $book->title;
            my $subtitle = $book->subtitle;
            if ($subtitle) {
                $title .= ": " . $subtitle;    # XXX - do we need the ": " ???
            }
            $titles{$bookID} = $title;
        }

        # 5) Find the book author
        #
        my $author = BookPub::DB::Item::BookContributor->GetAuthorLastNamesByBookID($bookID);

        # 6) Find the Division (Publisher)
        #
        my $publisherName;
        my $publisherID = $bookProduct->publisher_id;
        if ( $publisherNames{$publisherID} ) {
            $publisherName = $publisherNames{$publisherID};
        } else {
            my $publisher = BookPub::DB::Item::Publisher->Lookup( publisher_id => $publisherID );
            $publisherName = $publisher->name;
            $publisherNames{$publisherID} = $publisherName;
        }

        # 7) Find the Imprint
        #
        my $imprintName;
        my $imprintID = $bookProduct->imprint_id;
        if ( $imprintNames{$imprintID} ) {
            $imprintName = $imprintNames{$imprintID};
        } else {
            my $imprint = BookPub::DB::Item::Imprint->Lookup( imprint_id => $imprintID );
            $imprintName = $imprint->name;
            $imprintNames{$imprintID} = $imprintName;
        }

        # 8) Net Units
        # Note: this is aggregated
        #
        my $units = $sale->units;

        # If the units are negative, then the revenue will be reported as negative.
        # We take the abs to ensure our revenue calculations (below) work as expected.
        #
        my $revenue   = $sale->revenue;      # net revenue
        my $unitPrice = $sale->unit_price;

        # 9) Pub Price
        #
        my $pubPrice = abs( $sale->list_price );

        # We need to see if this price already includes tax.
        # If not, add it.
        if ( $pubPrice && !$priceIncludesTax ) {
            $pubPrice += $unitTax;
        }

        # Now round it off.
        $pubPrice = formatFixedPoint( $pubPrice, 2 );

        # 10) Pub Price Currency
        #
        my $listPriceCurrency = $sale->r_list_price_currency;
        if ( !$listPriceCurrency ) {
            $listPriceCurrency = $sale->currency_code;
        }

        # 11) Pub List Price
        #
        my $pubListPrice = abs( $sale->list_price );

        # We need to see if this price already includes tax.
        # If it does, subtract it.
        if ( $pubListPrice && $priceIncludesTax ) {
            $pubListPrice -= $unitTax;
        }

        # Now round it off.
        $pubListPrice = formatFixedPoint( $pubListPrice, 2 );

        # 12) Pub List Price Currency -- see $listPriceCurrency, above
        #

        # 13) Retailer Selling Price
        #
        my $retailerSellingPrice = abs( formatFixedPoint( $sale->r_purchase_price, 2 ) );

        # 14) Retailer Selling Price Currency
        #
        my $retailerSellingPriceCurrency = $sale->r_purchase_price_currency;

        # 15) Discount Amount
        #
        my $discountAmount = formatFixedPoint( ( $pubListPrice * abs( $sale->units ) ) - abs($revenue), 2 );    # discount across all units

        # 16) Pub Discount/Commission
        #
        my $discount = formatFixedPoint( 100 * $sale->discount, 2 );

        # 17) Revenue Amount
        # Note: this is aggregated; also, the PRD has this defined as $sale->units * $sale->revenue,
        # which isn't correct since $sale->revenue is the total -- not unit -- revenue.
        #
        my $revenueAmount = $revenue;

        # 18) Revenue Currency
        #
        my $currencyCode = $sale->currency_code;

        # 19) Territory Code
        #
        my $countryCode = uc($sale->country_code);

        # 20) Conversion to Payment Currency
        #
        # .. The fileNames hash is used to keep track if we've seen this file or not.  The first
        # .. time we encounter a file we'll check if an alternate conversion rate was entered and
        # .. if so, we'll calculate the converted payment amount.
        #
        my $fileName;
        my $altConversionRate;
        my $altConversionCurrency;
        my $altConversionRevenue;
        my $convertedPayment;

        if ( !$fileNames{$fileID} ) {
            my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );
            $fileName = $file->orig_file_name;
            $fileName =~ s/_/ /g;
            $fileNames{$fileID} = $fileName;

            my $inputConversionRates = BookPub::DB::Item::InputConversionRate->GetAllByFileID( $file->file_id() );
            while ( my $conversion = $inputConversionRates->next() ) {
                if ( $conversion->is_alternate ) {
                    my $_altRateCurrency = $conversion->currency;
                    my $_altRate         = $conversion->conversion_rate;
                    my $_altRevenue      = $conversion->revenue;
                    my $_parentCurrency  = $conversion->parent_currency;
                    $altFileCurrency{$fileID}{$_parentCurrency} = join( "\t", $_altRate, $_altRateCurrency, $_altRevenue );
                }
            }
        }

        # .. calculate the converted payment amount if this file has an alternate conversion rate
        #
        if ( exists $altFileCurrency{$fileID} && exists $altFileCurrency{$fileID}{$currencyCode} ) {
            ( $altConversionRate, $altConversionCurrency, $altConversionRevenue ) = split( "\t", $altFileCurrency{$fileID}{$currencyCode} );

            # when for this currency the alternative currency rate was not set
            $altConversionRate     ||= 0;
            $altConversionCurrency ||= '';
            $altConversionRevenue  ||= 0;

            $convertedPayment = formatFixedPoint( $altConversionRevenue * $altConversionRate, 2 );
        }

        # 21) GBP Amount
        # Note: this is aggregated
        #
        my $saleConversionRate = $sale->conversion_rate;
        my $gbpAmount          = $revenueAmount * $saleConversionRate;

        # 22) Transaction Type
        #
        my $transactionType;
        if ( $revenueAmount > 0 ) {
            $transactionType = "Sale";
        } elsif ( $revenueAmount < 0 ) {
            $transactionType = "Return";
        } else {
            $transactionType = "Gratis";
        }

        # 23) Purchase Type
        my $purchaseType = $sale->purchase_type;

        # 24) Raw Purchase Type
        my $rPurchaseType = $sale->r_purchase_type;

        # Keep track of some running totals.  This information is used by dump_royalty_report.pl script
        # to determine if the revenue values from the file and input_conversion_rate tables match
        # the revenue values calculated here.  Note that we're assuming that the client's base currency
        # is GBP.
        #
        $totalUnits += $sale->units;
        $totalRevenueUnrounded += $gbpAmount;

        # Aggregate rows for standard report
        #
        # NOTE: we are aggregating the units and net revenue.  When the reports are being
        # generated (below), we'll convert the total net revenue to GBP.  If an alternate
        # conversion rate was found for the file, we'll convert the total revenue to the
        # alternate currency.
        #
        $standardRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
          {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
          {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
          {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
          {$purchaseType}{$rPurchaseType}{units} += $units;

        $standardRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
          {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
          {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
          {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
          {$purchaseType}{$rPurchaseType}{revenue} += $revenueAmount;


        # Aggregate rows for publisher group reports
        #
        # my %hhcRows;       # HHC rows
        # my %otherRows;     # Other rows
        # my %suppRows;      # Supplemental rows
        #
        if ( $self->_isHHC($publisherID) ) {
            $hhcRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{units} += $units;

            $hhcRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{revenue} += $revenueAmount;
        } elsif ( $self->_isOther($publisherID) ) {
            $otherRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{units} += $units;

            $otherRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{revenue} += $revenueAmount;
        } else {
            $suppRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{units} += $units;

            $suppRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
              {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
              {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
              {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}{$saleConversionRate}
              {$purchaseType}{$rPurchaseType}{revenue} += $revenueAmount;
        }

    }    # sale loop

    #----------------------------------------------#
    #                                              #
    #   Generate the standard (full) output file   #
    #                                              #
    #----------------------------------------------#

    # First, let's make sure it has a home.
    my $baseDir = $self->_baseDir();

    # Making sure the group and owner are set to apache,
    # even when this is manually kicked off.
    my $uid = getpwnam "apache";
    my $gid = getgrnam "apache";

    # We have to check at a couple of levels because of the way we store these reports.
    my @baseDirPath = split( '/', $baseDir );
    pop(@baseDirPath);
    my $superBaseDir = join( '/', @baseDirPath );

    unless ( -e $superBaseDir or mkdir( $superBaseDir, 0755 ) ) {
        die "Unable to create $superBaseDir\n";
    }
    chown $uid, $gid, $superBaseDir;

    unless ( -e $baseDir or mkdir( $baseDir, 0755 ) ) {
        die "Unable to create $baseDir\n";
    }

    chown $uid, $gid, $baseDir;

    my $outFilePathStd = $self->filePath();

    my $outFileStd;
    unless ( open( $outFileStd, '>:utf8', $outFilePathStd ) ) {
        die "can't open output file ($outFilePathStd) - $!\n";
    }
    chown $uid, $gid, $outFilePathStd;

    my @headers = (
        "Sale Period",                        # A
        "Retailer",                           # B
        "ISBN 13",                            # C
        "Title",                              # D
        "Author",                             # E
        "Division",                           # F
        "Imprint",                            # G
        "Net Units",                          # H
        "Pub Price",                          # I
        "Pub Price Currency",                 # J
        "Pub List Price",                     # K
        "Pub List Price Currency",            # L
        "Retailer Selling Price",             # M
        "Retailer Selling Price Currency",    # N
        "Discount Amount",                    # O
        "Pub Discount/Commission",            # P
        "Revenue Amount",                     # Q
        "Revenue Currency",                   # R
        "Territory Code",                     # S
        "Conversion to Payment Currency",     # T
        "Payment Currency",                   # U
        "GBP Amount",                         # V
        "Transaction Type",                   # W
        "File ID",                            # X
        "Period ID",                          # Y
        "Purchase Type",                      # Z
        "Raw Purchase Type",                  # AA
    );
    print $outFileStd join( ",", @headers ), "\n";

    # The report hash must have the following structure:
    #
    #    $standardRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
    #       {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
    #       {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
    #       {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}
    #       {$saleConversionRate}{units}
    #
    #    $standardRows{$fileID}{$transactionType}{$isbn13}{$countryCode}{$serviceName}{$saleDate}{$title}
    #       {$author}{$publisherName}{$imprintName}{$pubPrice}{$listPriceCurrency}
    #       {$pubListPrice}{$retailerSellingPrice}{$retailerSellingPriceCurrency}
    #       {$discount}{$discountAmount}{$currencyCode}{$altConversionCurrency}{$altConversionRate}
    #       {$saleConversionRate}{revenue}
    #

    foreach my $fileID ( sort keys %standardRows ) {
        my $fileSales = $standardRows{$fileID};

        foreach my $transactionType ( sort { $b cmp $a } keys %$fileSales ) {
            my $transactionSales = $fileSales->{$transactionType};

            foreach my $isbn13 ( sort keys %$transactionSales ) {
                my $isbn13Sales = $transactionSales->{$isbn13};

                foreach my $countryCode ( sort keys %$isbn13Sales ) {
                    my $countrySales = $isbn13Sales->{$countryCode};

                    foreach my $serviceName ( sort keys %$countrySales ) {
                        my $serviceSales = $countrySales->{$serviceName};

                        foreach my $saleDate ( sort keys %$serviceSales ) {
                            my $saleDateSales = $serviceSales->{$saleDate};

                            foreach my $title ( sort keys %$saleDateSales ) {
                                my $titleSales = $saleDateSales->{$title};

                                foreach my $author ( sort keys %$titleSales ) {
                                    my $authorSales = $titleSales->{$author};

                                    foreach my $publisher ( sort keys %$authorSales ) {
                                        my $publisherSales = $authorSales->{$publisher};

                                        foreach my $imprint ( sort keys %$publisherSales ) {
                                            my $imprintSales = $publisherSales->{$imprint};

                                            foreach my $publisherPrice ( sort keys %$imprintSales ) {
                                                my $publisherPriceSales = $imprintSales->{$publisherPrice};

                                                foreach my $listPriceCurrency ( sort keys %$publisherPriceSales ) {
                                                    my $listPriceCurrencySales = $publisherPriceSales->{$listPriceCurrency};

                                                    foreach my $pubListPrice ( sort keys %$listPriceCurrencySales ) {
                                                        my $pubListPriceSales = $listPriceCurrencySales->{$pubListPrice};

                                                        foreach my $retailerSellingPrice ( sort keys %$pubListPriceSales ) {
                                                            my $retailerSellingPriceSales = $pubListPriceSales->{$retailerSellingPrice};

                                                            foreach
                                                              my $retailerSellingPriceCurrency ( sort keys %$retailerSellingPriceSales ) {
                                                                my $retailerSellingPriceCurrencySales = $retailerSellingPriceSales->{$retailerSellingPriceCurrency};

                                                                foreach my $discount ( sort keys %$retailerSellingPriceCurrencySales ) {
                                                                    my $discountSales = $retailerSellingPriceCurrencySales->{$discount};

                                                                    foreach my $discountAmount ( sort keys %$discountSales ) {
                                                                        my $discountAmountSales = $discountSales->{$discountAmount};

                                                                        foreach my $currencyCode ( sort keys %$discountAmountSales ) {
                                                                            my $currencyCodeSales = $discountAmountSales->{$currencyCode};

                                                                            foreach my $altConversionCurrency ( sort keys %$currencyCodeSales ) {
                                                                                my $altConversionCurrencySales = $currencyCodeSales->{$altConversionCurrency};

                                                                                foreach my $altConversionRate ( sort keys %$altConversionCurrencySales ) {
                                                                                    my $altConversionRateSales = $altConversionCurrencySales->{$altConversionRate};

                                                                                    foreach my $saleConversionRate ( sort keys %$altConversionRateSales ) {
                                                                                        my $purchaseTypes = $altConversionRateSales->{$saleConversionRate};

                                                                                        foreach my $purchaseType ( sort keys %$purchaseTypes ) {
                                                                                            my $rPurchaseTypes = $purchaseTypes->{$purchaseType};

                                                                                            foreach my $rPurchaseType ( sort keys %$rPurchaseTypes ) {
                                                                                                my $row = $rPurchaseTypes->{$rPurchaseType};

                                                                                                my $units         = $row->{units};
                                                                                                my $revenueAmount = $row->{revenue};

                                                                                                my $gbpAmount = formatFixedPoint(
                                                                                                    $revenueAmount * $saleConversionRate, 2 );

                                                                                                my $convertedPayment = $altConversionRate
                                                                                                    ? formatFixedPoint($revenueAmount * $altConversionRate, 2 )
                                                                                                    : '';

                                                                        # We don't want to show blank converted payment or alt conversion currency
                                                                        # info.  If we don't have a converted payment, then just show the file's
                                                                        # reported currency (FB5373).
                                                                        #
                                                                                                my $_convertedPayment;
                                                                                                my $_paymentCurrency;

                                                                                                if ( '' eq $convertedPayment ) {
                                                                                                    $_convertedPayment = $revenueAmount;
                                                                                                    $_paymentCurrency  = $currencyCode;
                                                                                                } else {
                                                                                                    $_convertedPayment = $convertedPayment;
                                                                                                    $_paymentCurrency  = $altConversionCurrency;
                                                                                                }

                                                                                                $_convertedPayment =
                                                                                                formatFixedPoint( $_convertedPayment, 2 );

                                                                                                $purchaseType = $purchaseTypeMap{$purchaseType} if exists $purchaseTypeMap{$purchaseType};

                                                                                                # output the data !!!

                                                                                                my @outputRow = (
                                                                                                    $saleDate,                     # A - Sale Period
                                                                                                    $serviceName,                  # B - Retailer
                                                                                                    $isbn13,                       # C - ISBN 13
                                                                                                    $title,                        # D - Title
                                                                                                    $author,                       # E - Author
                                                                                                    $publisher,                    # F - Division
                                                                                                    $imprint,                      # G - Imprint
                                                                                                    $units,                        # H - Net Units
                                                                                                    $publisherPrice,               # I - Pub Price
                                                                                                    $listPriceCurrency,            # J - Pub Price Currency
                                                                                                    $pubListPrice,                 # K - Pub List Price
                                                                                                    $listPriceCurrency,            # L - Pub List Price Currency
                                                                                                    $retailerSellingPrice,         # M - Retailer Selling Price
                                                                                                    $retailerSellingPriceCurrency, # N - Retailer Selling Price Currency
                                                                                                    $discountAmount,               # O - Discount Amount
                                                                                                    $discount,                     # P - Pub Discount/Commission
                                                                                                    $revenueAmount,                # Q - Revenue Amount
                                                                                                    $currencyCode,                 # R - Revenue Currency
                                                                                                    $countryCode,                  # S - Territory Code
                                                                                                    $_convertedPayment,            # T - Conversion to Payment Currency
                                                                                                    $_paymentCurrency,             # U - Payment Currency
                                                                                                    $gbpAmount,                    # V - GBP Amount
                                                                                                    $transactionType,              # W - Transaction Type
                                                                                                    $fileID,                       # X - File ID
                                                                                                    $periodID,                     # Y - Period ID
                                                                                                    $purchaseType,                 # Z - Purchase Type
                                                                                                    $rPurchaseType,                # AA - Raw Purchase Type
                                                                                                );

                                                                                                $csv->print( $outFileStd, \@outputRow );
                                                                                                print $outFileStd "\n";
                                                                                            } #rPurchaseType
                                                                                        } #purchaseType
                                                                                    }    #saleConversionRate
                                                                                }    #altConversionRate
                                                                            }    #altConversionCurrency
                                                                        }    #currencyCode
                                                                    }    #discountAmount
                                                                }    #discount
                                                            }    #retailerSellingPriceCurrency
                                                        }    #retailerSellingPrice
                                                    }    #pubListPrice
                                                }    #listPriceCurrency
                                            }    #publisherPrice
                                        }    #imprint
                                    }    #publisher
                                }    #author
                            }    #title
                        }    #saleDate
                    }    #serviceName
                }    #countryCode
            }    #isbn13
        }    #transactionType
    }    #fileID

    close($outFileStd);

    #Common::Log::Print("Report created: " . $self->filePath());

    #-----------------------------------------------#
    #                                               #
    #   Generate the publisher group output files   #
    #                                               #
    #-----------------------------------------------#
    my @headersPublisher = (
        'Sale Period',                       # A
        'Retailer',                          # B
        'ISBN 13',                           # C
        'Title',                             # D
        'Author',                            # E
        'Division',                          # F
        'Imprint',                           # G
        'Net Units',                         # H
        'Territory Code',                    # I
        'Conversion to Payment Currency',    # J
        'Payment Currency',                  # K
        'GBP Amount',                        # L
        'Purchase Type',                     # M
        'Raw Purchase Type',                 # N
    );

    my $numStd   = ( scalar keys %standardRows );
    my $numHHC   = ( scalar keys %hhcRows );
    my $numOther = ( scalar keys %otherRows );
    my $numSupp  = ( scalar keys %suppRows );

    # We only generate the publisher group report if there lines to report
    # !!! However, we should attempt to delete them every time.
    # !!! Otherise, we may end up with old reports mixed in with the new ones
    # !!! when regenerating for closed periods.

    # Bookpoint and LBS reports are deprecated, but I'm leaving these here so that we will delete them
    # when we regenerate the output files for a historical period.
    my $outFilePathBookpoint = $self->filePathBookpoint();

    if ( -e $outFilePathBookpoint ) {
        Common::Log::Print("$outFilePathBookpoint exists, removing it...");
        die "\nCan't remove $outFilePathBookpoint\n" unless ( unlink $outFilePathBookpoint );
    }

    my $outFilePathLBS = $self->filePathLBS();

    if ( -e $outFilePathLBS ) {
        Common::Log::Print("$outFilePathLBS exists, removing it...");
        die "\nCan't remove $outFilePathLBS\n" unless ( unlink $outFilePathLBS );
    }

    my $outFilePathHHC = $self->filePathHHC();

    if ( -e $outFilePathHHC ) {
        Common::Log::Print("$outFilePathHHC exists, removing it...");
        die "\nCan't remove $outFilePathHHC\n" unless ( unlink $outFilePathHHC );
    }

    if ( $numHHC > 0 ) {
        my $outFileHHC;
        unless ( open( $outFileHHC, '>:utf8', $outFilePathHHC ) ) {
            die "can't open output file ($outFilePathHHC) - $!\n";
        }
        chown $uid, $gid, $outFilePathHHC;

        print $outFileHHC join( ",", @headersPublisher ), "\n";

        $self->_generateReport( $csv, $outFileHHC, \%hhcRows, \%purchaseTypeMap );

        close($outFileHHC);

        Common::Log::Print("Report created: $outFilePathHHC");

    }    #HHC

    my $outFilePathOther = $self->filePathOther();

    if ( -e $outFilePathOther ) {
        Common::Log::Print("$outFilePathOther exists, removing it...");
        die "\nCan't remove $outFilePathOther\n" unless ( unlink $outFilePathOther );
    }

    if ( $numOther > 0 ) {
        my $outFileOther;
        unless ( open( $outFileOther, '>:utf8', $outFilePathOther ) ) {
            die "can't open output file ($outFilePathOther) - $!\n";
        }
        chown $uid, $gid, $outFilePathOther;

        print $outFileOther join( ",", @headersPublisher ), "\n";

        $self->_generateReport( $csv, $outFileOther, \%otherRows, \%purchaseTypeMap );

        close($outFileOther);

        Common::Log::Print("Report created: $outFilePathOther");

    }    #Other

    my $outFilePathSupp = $self->filePathSupplemental();

    if ( -e $outFilePathSupp ) {
        Common::Log::Print("$outFilePathSupp exists, removing it...");
        die "\nCan't remove $outFilePathSupp\n" unless ( unlink $outFilePathSupp );
    }

    if ( $numSupp > 0 ) {
        my $outFileSupp;
        unless ( open( $outFileSupp, '>:utf8', $outFilePathSupp ) ) {
            die "can't open output file ($outFilePathSupp) - $!\n";
        }
        chown $uid, $gid, $outFilePathSupp;

        print $outFileSupp join( ",", @headersPublisher ), "\n";

        $self->_generateReport( $csv, $outFileSupp, \%suppRows, \%purchaseTypeMap );

        close($outFileSupp);

        Common::Log::Print("Report created: $outFilePathSupp");

    }    #Supp

    # Going to send everything back in a hash so that it's
    # easy to add more stuff in later.
    #
    my %returnValues;
    $returnValues{totalUnits}            = $totalUnits;
    $returnValues{totalRevenueUnrounded} = $totalRevenueUnrounded;

    return (%returnValues);

}    # create

sub _getDateFromAppleFileName {
    my ( $self, $fileID ) = @_;

    my $date;

    my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );
    my $fileName = $file->orig_file_name;
    if ( $fileName =~ /[\s_](\d{2})(\d{2})[\s_]\w{2}/ ) {
        $date = $1 . "/" . "20" . $2;
    }

    return $date;
}

sub validateFile {
    my ( $self, $fileID ) = @_;

    # This is the hash we'll send back all of the data in.
    # We'll need to know exactly which fields are missing.
    my %validation;

    # If everything is good, this is all we'll send back.
    $validation{isValid} = 1;

    my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );

    # Apple sale dates can sometimes span multiple months,
    # so we're just going to grab the date from the file name for them.
    if ( $file->service_id == 9 ) {
        my $date = $self->_getDateFromAppleFileName($fileID);
        if ( !$date ) {
            $validation{isValid} = undef;
            $validation{"Reporting Period"} = "missing";
        }
    }

    return %validation;
}

###
1;    #
###