#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------

package RPS::Report::Dynamic::Sales::Base;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/report/lib';
use lib '/app/tools/rps/lib';
use lib '/app/tools/appuser/lib';
use lib '/app/tools/data_classes/lib';

use Data::Dumper;
use Common::Log;
use Common::Assert;
use Common::Client;
use File::Path;
use AppUser::DB::Item::AccessLevel;
use Report::Dynamic::Parameters;
use Product::ProductAlbum;
use Product::ProductTrack;
use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::ClientOptions;
use RPS::DB::Item::Label;
use RPS::DB::Item::LabelPayee;
use RPS::DB::Item::Publisher;
use RPS::DB::Item::CAPublisher;
use RPS::DB::Item::Service;
use RPS::DB::Item::Product;
use RPS::DB::Item::Track;
use RPS::DB::Item::DynamicReport::Sales::Base;
use RPS::DB::Item::DynamicReport::Sales::ByRoyaltyRun;
use RPS::File::Sale;

use base 'Report::Dynamic::DirectQuery';

#
# Really just want to move some of the formatting methods into a common place.
# I thought about creating a base RPS::Report::Dynamic class, but I don't want
# to _assume_ that every sort of report will inherit from SingleQuery.
#
# But anything that requires 'RPS' data should NOT be moved into the Report namespace.
#

sub Group        { 'Sales'; }
sub FilenameType { 'Sales' }

use constant kFieldSaleSaleID     => 0;
use constant kFieldFileServiceID  => 1;
use constant kFieldFileFileID     => 2;
use constant kFieldSaleServiceID  => 3;
use constant kFieldProductID      => 4;
use constant kFieldProductType    => 5;
use constant kFieldFormatType     => 6;
use constant kFieldMediaType      => 7;
use constant kFieldCurrencyCode   => 8;
use constant kFieldConversionRate => 9;
use constant kFieldCountryCode    => 10;
use constant kFieldPrice          => 11;
use constant kFieldFree           => 12;
use constant kFieldUnits          => 13;
use constant kFieldPeriodBegin    => 14;
use constant kFieldPeriodEnd      => 15;
use constant kFieldSales          => 16;
use constant kFieldReturns        => 17;
use constant kFieldTotalRevenue   => 18;
use constant kFieldGrossRevenue   => 19;
use constant kFieldDistFee        => 20;
use constant kFieldFileName       => 21;
use constant kFieldLineNumber     => 22;

use constant kFieldChannel        => 23;
use constant kFieldPriceLevel     => 24;
use constant kFieldWholesalePrice => 25;
use constant kFieldRetailPrice    => 26;

use constant kFieldFilePeriodName => 27;
use constant kFieldFilePeriodID   => 28;
use constant kFieldProductCode    => 29;
use constant kFieldProductAssetID => 30;

use constant kFieldLabelID         => 31;
use constant kFieldAlbumName       => 32;
use constant kFieldClientAlbumID   => 33;
use constant kFieldCatalogID       => 34;
use constant kFieldAlbumCustom1    => 35;
use constant kFieldAlbumCustom2    => 36;
use constant kFieldAlbumCustom3    => 37;
use constant kFieldAlbumID         => 38;
use constant kFieldProductTitle    => 39;
use constant kFieldReleaseDate     => 40;
use constant kFieldUPC             => 41;
use constant kFieldUPCAlt          => 42;
use constant kFieldAlbumArtistName => 43;

use constant kFieldClientTrackID   => 44;
use constant kFieldTrackName       => 45;
use constant kFieldTrackCustom1    => 46;
use constant kFieldTrackCustom2    => 47;
use constant kFieldTrackCustom3    => 48;
use constant kFieldTrackArtistName => 49;
use constant kFieldISRC            => 50;
use constant kFieldDiscNumber      => 51;
use constant kFieldDiscTrack       => 52;

# dynamic fields for US/CA Mechanicals
use constant kFieldPublisher          => 53;
use constant kFieldPublisherClientNo  => 54;
use constant kFieldRsPublisherPayeeId => 55;
use constant kFieldAgent              => 56;
use constant kFieldAgentClientNo      => 57;
use constant kFieldRsAgentPayeeId     => 58;
use constant kFieldAdmin              => 59;
use constant kFieldAdminClientNo      => 60;
use constant kFieldRsAdminPayeeId     => 61;
use constant kFieldRunNameMech        => 62;
use constant kFieldPayorNameMech      => 63;

# dynamic fields for Artist or Label run
use constant kFieldPayee              => 53;
use constant kFieldClientAccountNo    => 54;
use constant kFieldRsPayeeId          => 55;
use constant kFieldRunNameAoL         => 56;
use constant kFieldPayorNameAoL       => 57;

my %channel_level_map = (
    0 => {
        price_level => "Unknown",
    },
    1 => {
        price_level => "Full",
        channel => "Retail"
    },
    2 => {
        price_level => "Mid",
        channel => "Military"
    },
    3 => {
        price_level => "Budget",
        channel => "Club"
    },
    4 => {
        channel => "Mail Order"
    },
    5 => {
        channel => "Direct"
    },
    6 => {
        price_level => "Promo"
    },
);

my $showGrossRevenue = RPS::DB::Item::ClientOptions->Get( 'gross_revenue' );

sub UserLevelIsValid {
    my ( $class, $userLevel ) = @_;
    my %validLevels = (
        AppUser::DB::Item::AccessLevel::kRSAdmin()          => 1,
        AppUser::DB::Item::AccessLevel::kRSAccountRep()     => 1,
        AppUser::DB::Item::AccessLevel::kRoyalty()          => 1,
        AppUser::DB::Item::AccessLevel::kAdminCatalogView() => 1,
        AppUser::DB::Item::AccessLevel::kAdmin()            => 1,
    );

    return $validLevels{$userLevel};
}

sub _getFilter {
    my ( $self, $formHash ) = @_;

    my %fieldMap = (
        'album_title'     => 'album-title',
        'album_artist'    => 'album-artist',
        'client_album_id' => 'client-album-id',
        'album_custom_1'  => 'album-custom-1',
        'album_custom_2'  => 'album-custom-2',
        'album_custom_3'  => 'album-custom-3',
        'catalog_#'       => 'catalog-#',
        'isrc'            => 'isrc',
        'label_name'      => 'label-name',
        'media_type'      => 'media-type',
        'product_type'    => 'product-type',
        'product_format'  => 'product-format',
        'product_title'   => 'product-title',
        'release_date'    => 'release-date',
        'track_title'     => 'track-title',
        'track_artist'    => 'track-artist',
        'client_track_id' => 'client-track-id',
        'track_custom_1'  => 'track-custom-1',
        'track_custom_2'  => 'track-custom-2',
        'track_custom_3'  => 'track-custom-3',
        'upc'             => 'upc',
        'upc_alt'         => 'upc-alt',
        'currency_code'   => 'currency-code',
        'distributor'     => 'distributor',
        'service_name'    => 'service-name',
        'territory'       => 'territory',
        'file_name'       => 'file-name',
    );

    foreach my $key ( @{ $formHash->getKeys() } ) {
        my $field = $formHash->getHash()->{$key};
        my $value = $field->Value();
        if ( $field->Name() eq 'release_date' ) {
            my $date = Common::FormObject::Scalar::Date->new();
            $date->access($value);
            $value = $date->access();
        }
        my $colIndex = 0;
        foreach my $column ( @{ $self->_columns() } ) {
            if ( $column->{header}{name} eq $fieldMap{ $field->Name() } ) {
                last;
            }
            $colIndex++;
        }

        my $op;
        if ($field->Op() eq 'equal') {
            $op = '=';
        } elsif ($field->Op() eq 'not_equal') {
            $op = '!=';
        } elsif ($field->Op() eq 'before') { # This is meant to be "on or before"
            $op = '<=';
        } elsif ($field->Op() eq 'after') { # This is meant to be "on or after"
            $op = '>=';
        } else {
            die "Unknown op: $field->Op()";
        }

        if ( $colIndex < scalar( @{ $self->_columns() } ) ) {
            push( @{ $self->{_filter}{$field->Name()}{ $op } }, $value );
        }
    }
}

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

    if ( $self->_getParamCriteriaByMethod('CatalogFilter') ) {
        $self->_getFilter( $self->_getParamCriteriaByMethod('CatalogFieldList') );
    }

    if ( $self->_getParamCriteriaByMethod('SourceFilter') ) {
        $self->_getFilter( $self->_getParamCriteriaByMethod('SourceFieldList') );
    }
}

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

    Common::Log::Print("_preCreateFiles start");

    $self->_getFilters();
    $self->_cacheLabels();
    $self->_cacheServices();

    Common::Log::Print("SUPER::_preCreateFiles start");

    $self->SUPER::_preCreateFiles();

    Common::Log::Print("SUPER::_preCreateFiles end");

    Common::Log::Print("_preCreateFiles end");
}

sub _runQuery {
    my ( $self, $sth, $fh ) = @_;

    Common::Log::Print("_runQuery start");

    my $consolidateReport = $self->_getParamCriteriaByMethod('Consolidate') eq 'None' ? 0 : 1;

    while ( my $row = $sth->fetchrow_arrayref() )    # less friendly/maintainable, but fetchrow_hashref is roughly 25% slower
    {
        my @lineData;

        # extra fields related to payee detail
        $self->_appendExtraData($row, \@lineData);

        push( @lineData, $self->{_labelCache}{ $row->[ kFieldLabelID() ] } );
        push( @lineData, $row->[ kFieldSaleServiceID() ] );
        push( @lineData, $self->{_serviceCache}{ $row->[ kFieldSaleServiceID() ] } );
        push( @lineData,
              $row->[ kFieldFileServiceID() ] != $row->[ kFieldSaleServiceID() ]
            ? $self->{_serviceCache}{ $row->[ kFieldFileServiceID() ] }
            : '' );
        push( @lineData, $consolidateReport ? '' : $row->[ kFieldFileName ] );
        push( @lineData, $consolidateReport ? '' : $row->[ kFieldFileFileID() ] );
        push( @lineData, $consolidateReport ? '' : $row->[ kFieldFilePeriodName() ] );
        push( @lineData, $consolidateReport ? '' : $row->[ kFieldFilePeriodID() ] );
        push( @lineData, $row->[ kFieldCountryCode() ] );
        push( @lineData, $row->[ kFieldPeriodBegin() ] );
        push( @lineData, $row->[ kFieldPeriodEnd() ] );
        push( @lineData, RPS::DB::Item::Product->ProductTypeIDToString( $row->[ kFieldProductType() ] ) );
        push( @lineData, RPS::File::Sale::GetFormatType( $row->[ kFieldFormatType() ] ) );
        push( @lineData, $row->[ kFieldCatalogID() ] );
        push( @lineData, $row->[ kFieldProductTitle() ] );
        push( @lineData, $row->[ kFieldProductCode() ] );
        push( @lineData, $row->[ kFieldUPC() ] );
        push( @lineData, $row->[ kFieldProductID() ] );
        push( @lineData, $row->[ kFieldClientAlbumID() ] );
        push( @lineData, $row->[ kFieldAlbumName() ] );
        push( @lineData, $row->[ kFieldAlbumArtistName() ] );
        my $releaseDate = $row->[ kFieldReleaseDate() ] || '';
        $releaseDate = '' if ( $releaseDate eq '0000-00-00' );
        push( @lineData, $releaseDate );
        push( @lineData, $row->[ kFieldUPCAlt() ] );
        push( @lineData, $row->[ kFieldAlbumCustom1() ] );
        push( @lineData, $row->[ kFieldAlbumCustom2() ] );
        push( @lineData, $row->[ kFieldAlbumCustom3() ] );

        # album id
        push( @lineData, $row->[ kFieldAlbumID() ] );

        if ( $row->[ kFieldProductType() ] == 4 ) {
            push( @lineData, $row->[ kFieldISRC() ] );
            push( @lineData, $row->[ kFieldClientTrackID() ] );
            push( @lineData, $row->[ kFieldDiscNumber() ] );
            push( @lineData, $row->[ kFieldDiscTrack() ] );
            push( @lineData, $row->[ kFieldTrackName() ] );
            push( @lineData, $row->[ kFieldTrackArtistName() ] );
            push( @lineData, $row->[ kFieldTrackCustom1() ] );
            push( @lineData, $row->[ kFieldTrackCustom2() ] );
            push( @lineData, $row->[ kFieldTrackCustom3() ] );
            push( @lineData, $row->[ kFieldProductAssetID() ] );
        } else {
            push( @lineData, (undef) x 10 );
        }

        my $units;
        my $price;
        my $extPrice;
        my $nativePrice;
        my $conversionRate = $row->[ kFieldConversionRate() ];
        my $distFee = $row->[ kFieldDistFee() ];
        my $grossRevenue = $row->[ kFieldGrossRevenue() ];

        if ( $row->[ kFieldProductType() ] == 3 || $row->[ kFieldProductType() ] == 4 ) {
            $units = $row->[ kFieldUnits() ];
            $price = $row->[ kFieldPrice() ];
            my $priceCalc = $price || 0;
            if ($units != 0) {
                $extPrice = $units * $priceCalc;
            } else {
                $extPrice = $priceCalc;
            }
            $nativePrice = $priceCalc * $conversionRate;
        } else {
            $units       = $row->[ kFieldSales() ] - $row->[ kFieldReturns() ];
            $price       = undef;
            $extPrice    = $row->[ kFieldTotalRevenue() ];
            $nativePrice = undef;
        }
        my $nativeExtPrice = $conversionRate * $extPrice;
        my $nativeNetPrice = $nativeExtPrice * ( 1 - $distFee );

        my $channel = $channel_level_map{$row->[kFieldChannel()]}->{channel};
        my $price_level = $channel ? $channel_level_map{$row->[kFieldPriceLevel()]}->{price_level} : undef;

        push( @lineData, $channel);
        push( @lineData, $price_level);
        push( @lineData, $row->[kFieldProductType] !~ /^3|4$/ ? $row->[ kFieldRetailPrice() ] : undef);
        push( @lineData, $row->[ kFieldWholesalePrice() ] );

        push( @lineData, $units );

        my $unitGrossPrice;
        if ( $showGrossRevenue ) {
            if ( $row->[ kFieldProductType() ] == 3 || $row->[ kFieldProductType() ] == 4 ) {
                if ($units != 0) {
                    $unitGrossPrice = $grossRevenue / $units;
                } else {
                    $unitGrossPrice = $grossRevenue;
                }
            }
            push( @lineData, $consolidateReport ? '' : $unitGrossPrice ); #unit-gross-price
            push( @lineData, $consolidateReport ? '' : $grossRevenue ); #ext-gross-price
        }

        push( @lineData, $consolidateReport ? '' : $price );
        push( @lineData, $consolidateReport ? '' : $extPrice );
        push( @lineData, $row->[ kFieldCurrencyCode() ] );
        push( @lineData, $consolidateReport ? '' : $conversionRate );

        if ( $showGrossRevenue ) {
            my $curUnitGrossPrice;
            if ( $row->[ kFieldProductType() ] == 3 || $row->[ kFieldProductType() ] == 4 ) {
                $curUnitGrossPrice = $unitGrossPrice * $conversionRate;
            }
            push( @lineData, $consolidateReport ? '' : $curUnitGrossPrice ); #cur-unit-gross-price
            push( @lineData, $consolidateReport ? '' : $grossRevenue * $conversionRate ); #cur-ext-gross-price
        }

        push( @lineData, $consolidateReport ? '' : $nativePrice );
        push( @lineData, $consolidateReport ? '' : $nativeExtPrice );
        push( @lineData, $row->[ kFieldFree() ] );
        push( @lineData, $consolidateReport ? '' : $distFee );

        if ( $showGrossRevenue ) {
            push( @lineData, $grossRevenue * $conversionRate * ( 1 - $distFee ) ); #cur-gross-price
        }

        push( @lineData, $nativeNetPrice );

        if (!$consolidateReport) {
            push( @lineData, $row->[ kFieldMediaType() ] );
            push( @lineData, $row->[ kFieldLineNumber() ] );
            push( @lineData, $row->[ kFieldSaleSaleID() ] );
        }

        no warnings 'uninitialized';    # just here
        $fh->print( join( "\t", @lineData ) . "\n" );
    }
    Common::Log::Print("_runQuery end");
}

sub _appendExtraData {
    my ($self, $row, $aLineData) = @_;

    my $runType     = $self->_getParamCriteriaByMethod('RunType')     || '';
    my $payeeDetail = $self->_getParamCriteriaByMethod('PayeeDetail') || '';
    my $royaltyRun  = $self->_getParamCriteriaByMethod('RunType')     || '';
    my $rsRunID     = $self->_getParamCriteriaByMethod('RunName')     || '';
    $rsRunID =~ s/[^\d]//g;

    if ( $runType =~ /^(?:US|CA) Mechanicals$/i ) {
        if ( $payeeDetail =~ /^(?:Single Payee|All Payees)$/i ) {
            push( @$aLineData, $row->[ kFieldAgent() ] );                 # agent
            push( @$aLineData, $row->[ kFieldAgentClientNo() ] );         # agent-client-no
            push( @$aLineData, $row->[ kFieldRsAgentPayeeId() ] );        # rs-agent-payee-id
            push( @$aLineData, $row->[ kFieldAdmin() ] );                 # admin
            push( @$aLineData, $row->[ kFieldAdminClientNo() ] );         # admin-client-no
            push( @$aLineData, $row->[ kFieldRsAdminPayeeId() ] );        # rs-admin-payee-id
            push( @$aLineData, $row->[ kFieldPublisher() ] );             # publisher
            push( @$aLineData, $row->[ kFieldPublisherClientNo() ] );     # publisher-client-no
            push( @$aLineData, $row->[ kFieldRsPublisherPayeeId() ] );    # rs-publisher-payee-id
        }

        # common fields are always presented
        push( @$aLineData, $row->[ kFieldRunNameMech() ] );               # run-name
        push( @$aLineData, $row->[ kFieldPayorNameMech() ] );             # payor-name
        push( @$aLineData, $royaltyRun );                                 # royalty-run
        push( @$aLineData, $rsRunID);                                     # rs-run-id
    } elsif ( $runType =~ /^(?:Artist\/Producer|Label)$/i ) {
        if ( $payeeDetail =~ /^(?:Single Payee|All Payees)$/i ) {
            push( @$aLineData, $row->[ kFieldPayee() ] );                 # payee
            push( @$aLineData, $row->[ kFieldClientAccountNo() ] );       # client-account-no
            push( @$aLineData, $row->[ kFieldRsPayeeId() ] );             # rs-payee-id
        }

        # common fields are always presented
        push( @$aLineData, $row->[ kFieldRunNameAoL() ] );                # run-name
        push( @$aLineData, $row->[ kFieldPayorNameAoL() ] );              # payor

        push( @$aLineData, $royaltyRun );                                 # royalty-run
        push( @$aLineData, $rsRunID );                                    # rs-run-id
    }

    return 1;
}

sub _cacheLabels {
    my ( $self, $id ) = @_;

    Common::Log::Print("_cacheLabels start");

    my $allLabels = RPS::DB::Item::Label->GetAll();
    while ( my $label = $allLabels->next() ) {
        my $labelID = $label->label_id();
        $self->{_labelCache}{$labelID} = $label->label_name();
    }

    Common::Log::Print("_cacheLabels end");
}

sub _cacheServices {
    my ( $self, $id ) = @_;

    Common::Log::Print("_cacheServices start");

    my $allServices = RPS::DB::Item::Service->GetAll();
    while ( my $service = $allServices->next() ) {
        my $serviceID = $service->service_id();
        $self->{_serviceCache}{$serviceID} = $service->service_name();
    }

    Common::Log::Print("_cacheServices end");
}

# Similar to Common::NameCase::camel, but preserve spaces.
# E.g., "album_name" -> "Album Name"
sub _prettyField {
    my $self = shift;
    my $text = shift;
    $text =~ s/_/ /g;
    $text =~ s{ \b (\w)   }{\u$1}gox;
    $text;
}

sub _prettyOp {
    my $self = shift;
    my $text = shift;
    return
        ( $text eq 'equal' )     ? 'is'
      : ( $text eq 'not_equal' ) ? 'is not'
      :                            $text;
}

sub _footer {
    my ($self) = @_;
    my @footer;

    if ( $self->_getParamCriteriaByMethod('CatalogFilter') ) {
        my @where;
        my $formHash = $self->_getParamCriteriaByMethod('CatalogFieldList');
        foreach my $key ( @{ $formHash->getKeys() } ) {
            my $field  = $formHash->getHash()->{$key};
            my $value  = $field->Value();
            my $filter = $self->_prettyField( $field->Name() );
            my $op     = $self->_prettyOp( $field->Op() );
            push @where, "$filter $op '$value'";
        }
        push @footer, 'Select Catalog Include Catalog Where ' . join( ', ', @where );
    } else {
        push @footer, 'Select Catalog Include All Catalog';
    }

    if ( $self->_getParamCriteriaByMethod('SourceFilter') ) {
        my @where;
        my $formHash = $self->_getParamCriteriaByMethod('SourceFieldList');
        foreach my $key ( @{ $formHash->getKeys() } ) {
            my $field  = $formHash->getHash()->{$key};
            my $value  = $field->Value();
            my $filter = $self->_prettyField( $field->Name() );
            my $op     = $self->_prettyOp( $field->Op() );
            push @where, "$filter $op '$value'";
        }
        push @footer, 'Select Sales Source Include Sales Where ' . join( ', ', @where );

    } else {
        push @footer, 'Select Sales Source Include All Sales';
    }

    my $showByTypeParam = $self->_getParamCriteriaByMethod('ShowByType');

    my $startDate = $self->_getParamCriteriaByMethod('StartDate');
    my $endDate   = $self->_getParamCriteriaByMethod('EndDate');

    if ( 'By Date' eq $showByTypeParam ) {
        if ( !$startDate && !$endDate ) {
            push @footer, 'Show Sales By Date: No dates specified';
        } else {
            my @filter;
            if ($startDate) {
                push @filter, "from $startDate";
            }
            if ($endDate) {
                push @filter, "to $endDate";
            }

            push @footer, 'Show Sales By Date ' . join( " ", @filter );
        }

    } elsif ( 'By Royalty Run' eq $showByTypeParam ) {
        push @footer, 'Show Sales By Royalty Run';

        my $runType     = $self->_getParamCriteriaByMethod('RunType');
        my $payorID     = $self->_getParamCriteriaByMethod('Payor');
        my $runID       = $self->_getParamCriteriaByMethod('RunName');
        my $payeeDetail = $self->_getParamCriteriaByMethod('PayeeDetail');
        my $payeeID     = $self->_getParamCriteriaByMethod('PayeeID');

        my $payorName;
        my $runName;

        if ( $self->parameters()->{RunData} ) {

            # Convert the runType to the key used in RunData
            my %runTypeMap = (
                'Artist/Producer' => 'ArtistRoyalty',
                'US Mechanicals'  => 'USMechanicals',
                'CA Mechanicals'  => 'CAMechanicals',
                'UK Mechanicals'  => 'UKMechanicals',
                'Label'           => 'LabelRoyalty',
            );
            my $runKey = $runTypeMap{$runType};

            my $payorList = $self->parameters()->{RunData}{$runKey}{Payor};
            foreach my $payor ( @{$payorList} ) {
                if ( $payor->{PayorID} == $payorID ) {
                    $payorName = $payor->{Name};
                    $self->{payor_name} = $payorName;

                    foreach my $run ( @{ $payor->{Run} } ) {
                        # remove prefix from run_id if exists (US/CA Mechanicals)
                        $_ =~ s/(?:[a-z]{3}_)?(\d+)$/$1/ for $runID, $run->{RunID};

                        if ( $run->{RunID} == $runID ) {
                            $runName = $run->{Label};
                            $self->{run_name} = $runName;
                            last;
                        }
                    }
                }
            }
        }

        my $runFilter = '';
        if ( $self->parameters()->{Criteria}{RunType} ) {
            $runFilter = "Royalty Run $runType";
        }
        if ( $self->parameters()->{Criteria}{Payor} ) {
            $runFilter .= ", Payor '$payorName'";
        }

        if ( $self->parameters()->{Criteria}{RunName} ) {
            $runFilter .= ", Run Name '$runName'";
        }

        push @footer, $runFilter;

        my $payeeDetailValue = $self->_footerExtendByPayeeDetail($runType, $payeeDetail, $payeeID);
        push @footer, $payeeDetailValue if $payeeDetailValue;

    } elsif ( 'By Sales Period' eq $showByTypeParam ) {
        my $startPeriod = $self->_getParamCriteriaByMethod('StartPeriod') // '';
        my $endPeriod   = $self->_getParamCriteriaByMethod('EndPeriod')   // '';
        if ( $startPeriod =~ /^\d+$/ && $endPeriod =~ /^\d+$/ ) {
            my $aPeriods = $self->parameters()->{SalesPeriods}{Period};
            my ($startPeriodName) = map { $_->{Name} } grep { $_->{PeriodID} == $startPeriod } @$aPeriods;
            my ($endPeriodName) = map { $_->{Name} } grep { $_->{PeriodID} == $endPeriod } @$aPeriods;

            $startPeriod = $startPeriod == 0 ? '' : "($startPeriod)";
            $endPeriod   = $endPeriod == 0 ? '' : "($endPeriod)";
            push @footer, "Show Sales By Sales Period from [$startPeriodName $startPeriod] to [$endPeriodName $endPeriod]";
        }
    }

    my $consolidateParam = $self->_getParamCriteriaByMethod('Consolidate');
    if ($consolidateParam) {
        if ( 'Month' eq $consolidateParam ) {
            $consolidateParam = 'Month';
        } elsif ( 'Quarter' eq $consolidateParam ) {
            $consolidateParam = 'Quarter';
        } elsif ( 'Year' eq $consolidateParam ) {
            $consolidateParam = 'Year';
        } else {
            $consolidateParam = 'None';
        }
    }
    push @footer, "Consolidate By $consolidateParam";

    return \@footer;
}

sub _footerExtendByPayeeDetail {
    my ($self, $runType, $payeeDetail, $payeeID) = @_;

    return unless $runType;
    return unless $payeeDetail && $payeeDetail =~ /^(?:All Payees|Single Payee)$/i;

    return qq/Payee 'All'/ if $payeeDetail =~ /^All Payees$/i;

    # Single Payee option was selected
    return unless $payeeID;

    my $value;
    if ( $runType =~ /Artist/i ) {
        my $oPayee = RPS::DB::Item::ArtistPayee->Lookup( artist_payee_id => $payeeID );
        $value = sprintf "Payee '%s (%s)', RS Payee ID '%s'",
            $oPayee->name || '', $oPayee->client_account_id || '', $payeeID;

    } elsif ( $runType =~ /Label/i ) {
        my $oPayee = RPS::DB::Item::LabelPayee->Lookup( label_payee_id => $payeeID );
        $value = sprintf "Payee '%s (%s)', RS Payee ID '%s'",
            $oPayee->name || '', $oPayee->client_account_id || '', $payeeID;
    } elsif ( $runType =~ /US Mechanical/i ) {
        my $oPublisher = RPS::DB::Item::Publisher->Lookup( publisher_id => $payeeID );
        my $publisherType = "',";
        $publisherType    = ", Administrator'," if $oPublisher->is_admin;
        $publisherType    = ", Agency',"        if $oPublisher->is_agency;
        $value = sprintf "Payee '%s (%s)%s RS Payee ID '%s'",
            $oPublisher->publisher_name || '', $oPublisher->client_account_id || '', $publisherType, $payeeID;

    } elsif ( $runType =~ /CA Mechanical/i ) {
        my $oPublisher = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $payeeID );
        my $publisherType = "',";
        $publisherType    = ", Administrator'," if $oPublisher->is_admin;
        $publisherType    = ", Agency',"        if $oPublisher->is_agency;
        $value = sprintf "Payee '%s (%s)%s RS Payee ID '%s'",
            $oPublisher->publisher_name || '', $oPublisher->client_account_id || '', $publisherType, $payeeID;
    }

    return $value;
}

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

    Common::Log::Print("_getCollection start");

    my $showByType;
    my $showByTypeParam = $self->_getParamCriteriaByMethod('ShowByType');
    if ($showByTypeParam) {
        if ( 'By Date' eq $showByTypeParam ) {
            $showByType = 'by_date';
        } elsif ( 'By Royalty Run' eq $showByTypeParam ) {
            $showByType = 'by_royalty_run';
        } elsif ( 'By Sales Period' eq $showByTypeParam ) {
            $showByType = 'by_sales_period';
        }
    }

    my $startPeriod = $self->_getParamCriteriaByMethod('StartPeriod') // '';
    $startPeriod = -1 unless $startPeriod =~ /^\d+$/;

    my $endPeriod = $self->_getParamCriteriaByMethod('EndPeriod') // '';
    $endPeriod = -1 unless $endPeriod =~ /^\d+$/;


    my $consolidateParam = $self->_getParamCriteriaByMethod('Consolidate');
    Common::Log::Debug( "consolidateParam = " . $consolidateParam );
    if ($consolidateParam) {
        if ( 'Month' eq $consolidateParam ) {
            $consolidateParam = 'Month';
        } elsif ( 'Quarter' eq $consolidateParam ) {
            $consolidateParam = 'Quarter';
        } elsif ( 'Year' eq $consolidateParam ) {
            $consolidateParam = 'Year';
        } else {
            $consolidateParam = 'None';
        }
    }

    # this poorly named variable is the general product type, either digital,
    # physical, or digital and physical
    my $prodTypeType = $self->Type();

    # Dereference the parameters, see if we're constraining
    # the query by date somehow.
    #
    # !!! Don't want to just pass the CGI parameter straight through - That could
    # !!! cause problems...
    #
    my ( $runType, $payor, $runIDFlag, $runID, $payeeDetail, $payeeID );
    if ( $self->parameters()->{Criteria}{RunType} ) {
        $runType = $self->_getParamCriteriaByMethod('RunType');
    }

    if ( $self->parameters()->{Criteria}{Payor} ) {
        $payor = $self->_getParamCriteriaByMethod('Payor');
    }

    if ( $self->parameters()->{Criteria}{RunName} ) {
        my $runName = $self->_getParamCriteriaByMethod('RunName');
        if ( $runName && $runName =~ /^([a-z]{3})_(\d+)$/i ) {
            ($runIDFlag, $runID) = ($1, $2);
        } else {
            $runIDFlag = '';
            $runID = $runName;
        }
    }

    if ( $self->parameters()->{Criteria}{PayeeDetail} ) {
        $payeeDetail = $self->_getParamCriteriaByMethod('PayeeDetail');
    }

    if ( $self->parameters()->{Criteria}{PayeeID} ) {
        $payeeID = $self->_getParamCriteriaByMethod('PayeeID');
    }

    Common::Log::Print("_getCollection returning query");
    # If we have the $showByType = 'by_royalty_run' option, we need to use
    # a totally different query.
    #
    if ( 'by_royalty_run' eq $showByType ) {
        return RPS::DB::Item::DynamicReport::Sales::ByRoyaltyRun->ReportQuery(
            startDate     => $self->_getParamCriteriaByMethod('StartDate'),
            endDate       => $self->_getParamCriteriaByMethod('EndDate'),
            showByType    => $showByType,
            prodTypeType  => $prodTypeType,
            runType       => $runType,
            payor         => $payor,
            runID         => $runID,
            runIDFlag     => $runIDFlag || '',
            payeeDetail   => $payeeDetail,
            payeeID       => $payeeID,
            startPeriod   => $startPeriod,
            endPeriod     => $endPeriod,
            consolidateBy => $consolidateParam,
            filter        => $self->{_filter}
        );
    } else {
        return RPS::DB::Item::DynamicReport::Sales::Base->ReportQuery(
            startDate     => $self->_getParamCriteriaByMethod('StartDate'),
            endDate       => $self->_getParamCriteriaByMethod('EndDate'),
            showByType    => $showByType,
            prodTypeType  => $prodTypeType,
            runType       => $runType,
            payor         => $payor,
            runID         => $runID,
            payeeDetail   => $payeeDetail,
            payeeID       => $payeeID,
            startPeriod   => $startPeriod,
            endPeriod     => $endPeriod,
            consolidateBy => $consolidateParam,
            filter        => $self->{_filter}
        );
    }
}

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

    my @columns;
    $self->_payeeRunDetailColumn( \@columns );
    $self->_labelColumn( \@columns );
    $self->_serviceColumns( \@columns );
    $self->_fileColumns( \@columns );
    $self->_territoryColumns( \@columns );
    $self->_remainingColumns( \@columns );

    return \@columns;
}

sub _payeeRunDetailColumn {
    my ($self, $column) = @_;


    my $runType     = $self->_getParamCriteriaByMethod('RunType') || '';
    my $payeeDetail = $self->_getParamCriteriaByMethod('PayeeDetail') || '';

    return if $runType !~ /^(?:Artist\/Producer|Label|US Mechanicals|CA Mechanicals)$/i;
    return if $payeeDetail && $payeeDetail !~ /^(?:Single Payee|All Payees)$/i;

    if ( $runType =~ /^(?:Artist\/Producer|Label)$/i && $payeeDetail ) {
        # extra fields
        push @$column, (
            {
                header => {
                    name => 'payee',
                },
                column => {},
            },
            {
                header => {
                    name => 'client-account-no',
                },
                column => {},
            },
            {
                header => {
                    name => 'rs-payee-id',
                },
                column => {},
            }
        );
    } elsif ( $runType =~ /^(?:US|CA) Mechanicals$/i && $payeeDetail ) {
        # extra fields
        push @$column, (
            {
                header => {
                    name => 'agent',
                },
                column => {},
            },
            {
                header => {
                    name => 'agent-client-no',
                },
                column => {},
            },
            {
                header => {
                    name => 'rs-agent-payee-id',
                },
                column => {},
            },
            {
                header => {
                    name => 'admin',
                },
                column => {},
            },
            {
                header => {
                    name => 'admin-client-no',
                },
                column => {},
            },
            {
                header => {
                    name => 'rs-admin-payee-id',
                },
                column => {},
            },
            {
                header => {
                    name => 'publisher',
                },
                column => {},
            },
            {
                header => {
                    name => 'publisher-client-no',
                },
                column => {},
            },
            {
                header => {
                    name => 'rs-publisher-payee-id',
                },
                column => {},
            },
        );
    }

    # common fields
    push @$column, (
        {
            header => {
                name => 'run-name',
            },
            column => {},
        },
        {
            header => {
                name => 'payor',
            },
            column => {},
        },
        {
            header => {
                name => 'royalty-run',
            },
            column => {},
        },
        {
            header => {
                name => 'rs-run-id',
            },
            column => {},
        },
    );

    return;
}

sub _labelColumn {
    my ( $self, $columns ) = @_;

    push @$columns,
      ( {
            header => {
                name => 'label-name',
            },
            column    => {},
        },
      );
}

sub _serviceColumns {
    my ( $self, $columns ) = @_;

    push @$columns,
      ( {
            header => {
                name => 'service-id',
            },
            column => {},
        },
        {
            header => {
                name => 'service-name',
            },
            column    => {},
            sortOrder => 3,
        },
        {
            header => {
                name => 'distributor',
            },
            column    => {},
            sortOrder => 2,
        },
      );
}

sub _fileColumns {
    my ( $self, $columns ) = @_;

    push @$columns,
      ( {
            header => {
                name => 'file-name',
            },
            column    => {},
        },
        {
            header => {
                name => 'rs-file-id',
            },
            column    => {},
        },
        {
            header => {
                name => 'sales-period-name',
            },
            column    => {},
        },
        {
            header => {
                name => 'sales-period_id',
            },
            column    => {},
        },
      );
}

sub _territoryColumns {
    my ( $self, $columns ) = @_;

    push @$columns,
      ( {
            header => {
                name => 'territory',
            },
            column    => {},
            sortOrder => 4,
        },
      );
}

sub _remainingColumns {
    my ( $self, $columns ) = @_;

    push @$columns,
      ( {
            header => {
                name => 'period-begin',
            },
            column    => {},
            sortOrder => 0,
        },
        {
            header => {
                name => 'period-end',
            },
            column    => {},
            sortOrder => 1,
        },
        {
            header => {
                name => 'product-type',
            },
            column    => {},
        },
        {
            header => {
                name => 'product-format',
            },
            column    => {},
            sortOrder => 5,
        },
        {
            header => {
                name => 'catalog-#',
            },
            column => {}
        },
        {
            header => {
                name => 'product-title',
            },
            column => {}
        },
        {
            header => {
                name => 'product-id',
            },
            column => {},
        },
        {
            header => {
                name => 'upc',
            },
            column    => {},
            sortOrder => 10,
        },
        {
            header => {
                name => 'rs-product-id',
            },
            column => {},
            sortOrder => 6,
        },
        {
            header => {
                name => 'client-album-id',
            },
            column => {}
        },
        {
            header => {
                name => 'album-title',
            },
            column    => {},
        },
        {
            header => {
                name => 'album-artist',
            },
            column => {}
        },
        {
            header => {
                name => 'release-date',
            },
            column => {
                excel => 'date:Y-M-D',
            }
        },
        {
            header => {
                name => 'upc-alt',
            },
            column => {}
        },
        {
            header => {
                name => 'album-custom-1',
            },
            column => {}
        },
        {
            header => {
                name => 'album-custom-2',
            },
            column => {}
        },
        {
            header => {
                name => 'album-custom-3',
            },
            column => {}
        },
        {
            header => {
                name => 'rs-album-id',
            },
            column => {},
        },
        {
            header => {
                name => 'isrc',
            },
            column => {}
        },
        {
            header => {
                name => 'client-track-id',
            },
            column => {}
        },
        {
            header => {
                name => 'disc-no',
            },
            column      => {},
        },
        {
            header => {
                name => 'track-no',
            },
            column      => {},
        },
        {
            header => {
                name => 'track-title',
            },
            column    => {},
        },
        {
            header => {
                name => 'track-artist',
            },
            column => {}
        },
        {
            header => {
                name => 'track-custom-1',
            },
            column => {}
        },
        {
            header => {
                name => 'track-custom-2',
            },
            column => {}
        },
        {
            header => {
                name => 'track-custom-3',
            },
            column => {}
        },
        {
            header => {
                name => 'rs-track-id',
            },
            column => {},
        },
        {
            header => {
                name => 'sales-channel',
            },
            column => {},
            sortOrder => 7,
        },
        {
            header => {
                name => 'price-tier',
            },
            column => {},
            sortOrder => 8,
        },
        {
            header => {
                name => 'retail-price',
            },
            column => {
                excel => 'number:0.00######',
            },
            sortOrder => 9,
            sortNumeric => 1,
        },
        {
            header => {
                name => 'wholesale-price',
            },
            column => {
                excel => 'number:0.00######',
            },
            sortOrder => 10,
            sortNumeric => 1,
        },
        {
            header => {
                name => 'units',
            },
            column => {
                excel => 'number:0',
            },
            sum => 1,
        }
    );

    if ( $showGrossRevenue ) {
        push @$columns,
          ( {
                header => {
                    name => 'unit-gross-price',
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name => 'ext-gross-price',
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name => 'unit-net-price',
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name => 'ext-net-price',
                },
                column => {
                    excel => 'number:0.00######',
                }
            },
        );
    } else {
        push @$columns,
          ( {
                header => {
                    name => 'unit-price',
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name => 'ext-price',
                },
                column => {
                    excel => 'number:0.00######',
                }
            },
        );
    }

    push @$columns,
      ( {
            header => {
                name => 'currency-code',
            },
            column    => {},
            sortOrder => 11,
        },
        {
            header => {
                name => 'currency-conv',
            },
            column => {
                excel => 'number:0.00000000',
            },
        }
    );

    if ( $showGrossRevenue ) {
        push @$columns,
          ( {
                header => {
                    name   => '-unit-gross-price',
                    method => '_localizeHeader'
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name   => '-ext-gross-price',
                    method => '_localizeHeader'
                },
                column => {
                    excel => 'number:0.00000000',
                },
            },
            {
                header => {
                    name   => '-unit-net-price',
                    method => '_localizeHeader'
                },
                column => {
                    method => '_getNativePrice',
                    excel  => 'number:0.00000000',
                },
            },
            {
                header => {
                    name   => '-ext-net-price',
                    method => '_localizeHeader'
                },
                column => {
                    excel => 'number:0.00######',
                }
            },
        );
    } else {
        push @$columns,
          ( {
                header => {
                    name   => '-unit-price',
                    method => '_localizeHeader'
                },
                column => {
                    method => '_getNativePrice',
                    excel  => 'number:0.00000000',
                },
            },
            {
                header => {
                    name   => '-ext-price',
                    method => '_localizeHeader'
                },
                column => {
                    excel => 'number:0.00######',
                }
            }
        );
    }

    push @$columns,
      ( {
            header => {
                name => 'free',
            },
            column    => {},
            sortOrder => 12,
        },
        {
            header => {
                name => 'dist-fee',
            },
            column => {
                excel => 'number:0.000000',
            },
        }
    );

    if ( $showGrossRevenue ) {
        push @$columns,
          ( {
                header => {
                    name   => '-gross-price',
                    method => '_localizeHeader'
                },
                column => {
                    excel => 'number:0.00000000',
                },
                sum => 1,
            },
        );
    }

    push @$columns,
      ( {
            header => {
                name   => '-net-price',
                method => '_localizeHeader'
            },
            column => {
                excel => 'number:0.00######',
            },
            sum => 1,
        },
        {
            header => {
                name => 'media-type',
            },
            column    => {},
        },
        {
            header => {
                name => 'sales-line-#',
            },
            column => {
                excel => 'number:0',
            },
        },
        {
            header => {
                name => 'rs-sale-id',
            },
            column => {
                excel => 'number:0',
            },
        });
}

sub ClientTypeMask { return Common::Client::kDigitalAdvantage; }

sub _localizeHeader {
    my ( $self, $text ) = @_;

    my $locale       = Common::Client::Current()->Locale();
    my $currencyCode = lc( $locale->currencyFormat()->currencyCode() );

    return $currencyCode . $text;
}

sub _getParamCriteriaByMethod {
    my ($self, $method) = @_;

    return '' unless ref $self->parameters()->{Criteria};
    if ( $self->parameters()->{Criteria}->can($method) ) {
        return $self->parameters()->{Criteria}->$method();
    }

    # if AUTOLOAD functionality is used
    eval{ $self->parameters()->{Criteria}->$method() };
    unless ( my $e = $@ ) {
        return $self->parameters()->{Criteria}->$method();
    }

    return '';
}


sub _sort {
    my ( $self, $unsorted, $sorted ) = @_;

    # The sorting has been moved into the query,
    # so we'll just do a quick copy.
    my $sort = "nice cp $unsorted $sorted";

    Common::Log::Print("sort command: $sort");
    system($sort);
}

1;