#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# 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::Service;
use RPS::DB::Item::Product;
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 kFieldDistFee          => 19;
use constant kFieldFileName         => 20;

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_name'        => 'album-name',
        'album_artist'      => 'album-artist',
        'album_id'          => 'album-id',
        'album_custom_1'    => 'album-custom-1',
        'album_custom_2'    => 'album-custom-2',
        'album_custom_3'    => 'album-custom-3',
        'catalog_id'        => 'catalog-id',
        '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_name'        => 'track-name',
        'track_artist'      => 'track-artist',
        'track_id'          => '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',
        'country_code'      => '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++;
        }

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

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

    if ($self->parameters()->{Criteria}->CatalogFilter())
    {
        $self->_getFilter($self->parameters()->{Criteria}->CatalogFieldList());
    }

    if ($self->parameters()->{Criteria}->SourceFilter())
    {
        $self->_getFilter($self->parameters()->{Criteria}->SourceFieldList());
    }
}

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

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

    $self->SUPER::_preCreateFiles();
}

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

    while (my $row = $sth->fetchrow_arrayref())  # less friendly/maintainable, but fetchrow_hashref is roughly 25% slower
    {
        my @lineData;
        my $product = $self->{_productCache}{$row->[kFieldProductID()]};
        push(@lineData, $product->{LabelName});
        push(@lineData, $row->[kFieldSaleServiceID()]);
        push(@lineData, $self->{_serviceCache}{$row->[kFieldSaleServiceID()]});
        push(@lineData, $row->[kFieldFileServiceID()] != $row->[kFieldSaleServiceID()] ? $self->{_serviceCache}{$row->[kFieldFileServiceID()]} : '');

        # Stripping the split file designation from the file name.
        my $fileName = $row->[kFieldFileName()];
        if ($fileName =~ /^(.*)(-[a-z]+)(\.\w+)?$/)
        {
            $fileName = $1 . $3;
        }
        push(@lineData, $fileName);

        push(@lineData, $row->[kFieldCountryCode()]);
        push(@lineData, $row->[kFieldPeriodBegin()]);
        push(@lineData, $row->[kFieldPeriodEnd()]);
        push(@lineData, RPS::File::Sale::GetProductType($row->[kFieldProductType()]));
        push(@lineData, RPS::File::Sale::GetFormatType($row->[kFieldFormatType()]));
        push(@lineData, $product->{CatalogID});
        push(@lineData, $product->{ProductTitle});
        push(@lineData, $product->{UPC});
        push(@lineData, $product->{ClientAlbumID});
        push(@lineData, $product->{AlbumName});
        push(@lineData, $product->{ProductType} eq 'T' ? $product->{AlbumArtistName} : $product->{ArtistName});
        my $releaseDate = $product->{ReleaseDate} || '';
        $releaseDate = '' if ($releaseDate eq '0000-00-00');
        push(@lineData, $releaseDate);
        push(@lineData, $product->{UPCAlt}); 
        push(@lineData, $product->{AlbumCustom1});
        push(@lineData, $product->{AlbumCustom2});
        push(@lineData, $product->{AlbumCustom3});
        if ($product->{ProductType} eq 'T')
        {
            push(@lineData, $product->{ISRC});
            push(@lineData, $product->{ClientTrackID});
            push(@lineData, $product->{DiscNumber});
            push(@lineData, $product->{DiscTrack});
            push(@lineData, $product->{TrackName});
            push(@lineData, $product->{ArtistName});
            push(@lineData, $product->{TrackCustom1});
            push(@lineData, $product->{TrackCustom2});
            push(@lineData, $product->{TrackCustom3});
        }
        else
        {
            push(@lineData, (undef) x 9);
        }
        my $units;
        my $price;
        my $extPrice;
        my $conversionRate = $row->[kFieldConversionRate()];
        my $nativePrice;
        my $distFee = $row->[kFieldDistFee()];
        if ($row->[kFieldProductType()] eq 'A' || $row->[kFieldProductType()] eq 'T')
        {
            $units = $row->[kFieldUnits()];
            $price = $row->[kFieldPrice()];
            my $priceCalc = $price || 0;
            $extPrice = $units * $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);
        push(@lineData, $units);
        push(@lineData, $price);
        push(@lineData, $extPrice);
        push(@lineData, $row->[kFieldCurrencyCode()]);
        push(@lineData, $conversionRate);
        push(@lineData, $nativePrice);
        push(@lineData, $nativeExtPrice);
        push(@lineData, $row->[kFieldFree()]);
        push(@lineData, $distFee);
        push(@lineData, $nativeNetPrice);
        push(@lineData, $row->[kFieldMediaType()]);

        if (_filter($self, \@lineData))
        {
            no warnings 'uninitialized';    # just here
            $fh->print(join("\t", @lineData) . "\n");
        }
    }
}

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

    my $allProducts = RPS::DB::Item::Product->GetAll();
    while (my $product = $allProducts->next())
    {
        my $productID = $product->product_id();
        if (4 == $product->product_type_id())
        {
            my $trackProduct = Product::ProductTrack->new(product_id => $productID);

            # have to grab any lazily fetched data now
            $trackProduct->_load_product_track_info();

            $self->{_productCache}{$productID} = $trackProduct->GetObjectXML();
        }
        else
        {
            $self->{_productCache}{$productID} = Product::ProductAlbum->new(product_id => $productID)->GetObjectXML();
        }
    }
}

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

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

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

    $self->_getFilters();

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

    my $consolidateParam = $self->parameters()->{Criteria}->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, $runID);
    if( $self->parameters()->{Criteria}{RunType} ) { 
        $runType = $self->parameters()->{Criteria}->RunType();
    }

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

    if( $self->parameters()->{Criteria}{RunName} ) { 
        $runID = $self->parameters()->{Criteria}->RunName();
    }


    # 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->parameters()->{Criteria}->StartDate(),
            endDate          => $self->parameters()->{Criteria}->EndDate(),
            showByType       => $showByType,
            prodTypeType     => $prodTypeType,
            runType          => $runType,
            payor            => $payor,
            runID            => $runID,
            consolidateBy    => $consolidateParam,
        );
    }
    else
    {
        return RPS::DB::Item::DynamicReport::Sales::Base->ReportQuery
        (
            startDate        => $self->parameters()->{Criteria}->StartDate(),
            endDate          => $self->parameters()->{Criteria}->EndDate(),
            showByType       => $showByType,
            prodTypeType     => $prodTypeType,
            runType          => $runType,
            payor            => $payor,
            runID            => $runID,
            consolidateBy    => $consolidateParam,
        );
    }
}


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

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

    return \@columns;
}

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

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


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

    my $consolidate = $self->parameters()->{Criteria}->Consolidate();

    push @$columns, (
        { 
            header => { 
                name => 'service-id',
            },             
            column => { 
            }, 
        },
        { 
            header => { 
                name => 'service-name',
            },             
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 1 : 3),
        },
        { 
            header => { 
                name => 'distributor',
            },             
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 0 : 2),
        },
    );
}


sub _fileColumns
{
    my ($self, $columns) = @_;
    
    my $consolidate = $self->parameters()->{Criteria}->Consolidate();

    push @$columns, (
        { 
            header => { 
                name => 'file-name', 
            },   
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 2 : 4),
        },  
    );
}


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

    my $consolidate = $self->parameters()->{Criteria}->Consolidate();

    push @$columns, (
        { 
            header => { 
                name => 'territory',
            },             
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 3 : 5),
        },
    );
}


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

    my $consolidate = $self->parameters()->{Criteria}->Consolidate();

    push @$columns, (  
        { 
            header => { 
                name => 'period-begin', 
            },   
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 4 : 0),
        },
        { 
            header => { 
                name => 'period-end', 
            }, 
            column => { 
            }, 
            sortOrder => ($consolidate eq 'None' ? 5 : 1),
        },
        { 
            header => { 
                name => 'product-type', 
            },       
            column => { 
            }, 
            sortOrder => 7,
        },
        { 
            header => { 
                name => 'product-format', 
            },       
            column => { 
            }, 
            sortOrder => 8,
        },
        { 
            header => { 
                name => 'catalog-id', 
            },   
            column => { 
            } 
        },
        { 
            header => { 
                name => 'product-title', 
            },   
            column => { 
            } 
        },        
        { 
            header => { 
                name => 'upc', 
            },   
            column => { 
            }, 
            sortOrder => 10,
        },
        { 
            header => { 
                name => 'album-id', 
            },  
            column => { 
            } 
        },
        { 
            header => { 
                name => 'album-name', 
            },    
            column => { 
            }, 
            sortOrder => 9,
        },
        { 
            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 => 'isrc', 
            },           
            column => { 
            } 
        },
        { 
            header => { 
                name => 'track-id', 
            },           
            column => { 
            } 
        },
        { 
            header => { 
                name => 'disc-no', 
            },           
            column => { 
            }, 
            sortOrder => 11,
            sortNumeric => 1,
        },
        { 
            header => { 
                name => 'track-no', 
            },     
            column => { 
            }, 
            sortOrder => 12,
            sortNumeric => 1,
        },
        { 
            header => { 
                name => 'track-name', 
            },     
            column => { 
            }, 
            sortOrder => 13,
        },
        { 
            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 => 'units', 
            },     
            column => { 
                excel => 'number:0',
            }, 
            sum => 1,
        },
        { 
            header => { 
                name => 'unit-price', 
            },     
            column => { 
                excel => 'number:0.00000000',
            }, 
            sortOrder => 14,
            sortNumeric => 1,
        },
        { 
            header => { 
                name => 'ext-price', 
            },     
            column => { 
                excel => 'number:0.00######',
            },
            sum => 1,
        },
        { 
            header => { 
                name => 'currency-code', 
            },     
            column => { 
            }, 
            sortOrder => 15,
        },
        { 
            header => { 
                name => 'currency-conv', 
            },     
            column => { 
                excel => 'number:0.00000000',
            }, 
            sortOrder => 16,
            sortNumeric => 1,
        },
        { 
            header => { 
                name => '-unit-price', 
                method => '_localizeHeader'
            },     
            column => { 
                method => '_getNativePrice',
                excel => 'number:0.00000000',
            }, 
        },
        { 
            header => { 
                name => '-ext-price', 
                method => '_localizeHeader'
            },     
            column => { 
                excel => 'number:0.00######',
            },
            sum => 1,
        },
        { 
            header => { 
                name => 'free', 
            },     
            column => { 
            }, 
            sortOrder => 18,
        },
        { 
            header => { 
                name => 'dist-fee', 
            },     
            column => { 
                excel => 'number:0.000000',
            },
        },
        { 
            header => { 
                name => '-net-price', 
                method => '_localizeHeader'
            },     
            column => { 
                excel => 'number:0.00######',
            },
            sum => 1,
        },
        { 
            header => { 
                name => 'media-type', 
            },     
            column => { 
            }, 
            sortOrder => 17,
        },
    );
}

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 _filter
{
    my ($self, $lineData) = @_;

    if (exists $self->{_filter})
    {
        foreach my $key (keys %{$self->{_filter}})
        {
            my $match = 0;
            foreach my $op (keys %{$self->{_filter}{$key}})
            {
                $match = ($op eq 'equal') ? 0 : 1;
                foreach my $value (@{$self->{_filter}{$key}{$op}})
                {
                    $value = lc($value);
                    if ($op eq 'equal')
                    {
                        if ($value eq lc($lineData->[$key]))
                        {
                            $match = 1;
                            last;
                        }
                    }
                    elsif ($op eq 'not_equal')
                    {
                        return 0 if ($value eq lc($lineData->[$key]));
                    }
                    elsif ($op eq 'before')
                    {
                        return 0 if ($lineData->[$key] gt $value);
                    }
                    elsif ($op eq 'after')
                    {
                        return 0 if ($lineData->[$key] lt $value);
                    }
                }
            }
            return $match if !$match;
        }
    }

    return 1;
}
    

1;