#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package Raptor::Tracker::Command::Base;
use strict;
use warnings;
use Data::Dumper;

use POSIX;

use lib '/app/tools/common/lib';
use RSApache::Response::XSLT;
use Common::Assert;
use Common::Client;
use Common::RSApp;
use Common::File::UTF16;
use RSApache::Message;

use lib '/app/tools/sale_import/lib';
use File::Util;

use lib '/app/tools/appuser/lib';
use AppUser::DB::Item::AccessLevel;

use lib '/app/tools/rps/lib';
use RPS::Sale::File;
use RPS::Sale::Report;
use RPS::File::File;
use RPS::File::Sale;

use lib '/app/tools/data_classes/lib';
use Client::Service;
use Period::RoyaltyPeriod;

use lib '/app/tools/raptor/lib';
use Raptor::Command;
use base 'Raptor::Command';

# Request to block upload based on extension in case 10724
use constant kInvalidFileExtensions => qw( pdf jpg doc docx xml zip );

sub doFileListing {
    my ( $self, $period, $page ) = @_;

    my $periodID = $period->PeriodID;
    my $files    = RPS::File::Files->new();

    # get all open files for this period
    # we need to check for these values in a cookie.
    # we'll set up the default values for some in case there is no cookie.
    # This 'fileType' has nothing to do with the 'file_type_id' column in the file table...

    # default values
    my $fileType = 1;
    my $pageSize = 25;
    my $sortby;
    my $reverseSort;
    my $statusSort = 0;
    my $serviceID  = 0;
    my $getJson    = '';
    my $currencyCode = '';

    # get params from cookies
    if ( my $openFileCookie = $self->_getOpenFileCookie() ) {
        my @openFileSettings = split( /,/, $openFileCookie );
        $fileType    = $openFileSettings[0];
        $pageSize    = $openFileSettings[1];
        $sortby      = $openFileSettings[2];
        $reverseSort = $openFileSettings[3];
        $serviceID   = $openFileSettings[4];
    }

    # get passed params
    my $cmd = $self->getParam('c') || '';

    if ( $cmd =~ /^currency_conversion_entry$/i ) {
        $serviceID    = $self->getParam('serviceID')    || 0;
        $pageSize     = $self->getParam('pageSize')     || 25;
        $getJson      = $self->getParam('getJson')      || '';
        $currencyCode = $self->getParam('currencyCode') || '';
    }

    # Get list of services for filter options
    my ($aServices, $selectedServiceID) = $self->_getServiceListOptions($cmd, $serviceID, $periodID);
    $serviceID = $selectedServiceID;

    # JSON response to an AJAX request
    if ( $getJson && $cmd =~ /^currency_conversion_entry$/i ) {
        return $self->_getFilterEntires($aServices, $cmd, $getJson);
    }

    # Create OpenFileSettings XML object section
    $self->_createOpenFileSettingsSection($fileType, $pageSize, $sortby, $reverseSort, $serviceID, $aServices);

    # First we want to see if a page number was passed directly in. If not, we'll check the URL.
    # If it's not there either, go with a default of 1.
    my $pageNum;
    my $pageParam = $self->getParam('page');
    if ( defined $page ) {
        $pageNum = $page;
    } elsif ( defined $pageParam ) {
        $pageNum = $pageParam;
    } else {
        $pageNum = 1;
    }

    # Sorting by status has to be handled a little differently, so we'll pull that out here.
    if ( $sortby && $sortby eq 'status' ) {
        $sortby = '';
        $statusSort = $reverseSort == 1 ? 2 : 1;
    }

    # We may want to only select some of the files, so let's test here.
    if ( $cmd =~ /^currency_conversion_entry$/i ) {
        # sales_period_date_end = <0 - default | YYYY-MM-DD>,
        my $salesPeriodEndDate = $self->getParam('salesPeriodEndDate') || '';
        $salesPeriodEndDate = undef if $salesPeriodEndDate !~ /^\d{4}-\d{2}-\d{2}$/;

        # conversion_rate = <0 - not entered | 1 - all>
        my $conversionRate = $self->getParam('conversionRate') || 0;
        my @conversionRateCondition = (
            table_join    => 'LEFT JOIN user_input_conversion_rate AS uicr ON f.file_id = uicr.file_id',
            filter_type4  => 'uicr.conversion_rate',
            filter_value4 => ' = 0',
        ) unless $conversionRate;

        # currency code = <0 - all | mul | eur | ...>
        $currencyCode = '' if $currencyCode !~ /^[a-z]{3}$/i;

        my $fliterString = 'f.revenue != 0';
        if ($currencyCode) {
            $fliterString .= " AND f.currency_code = '$currencyCode'";
        }

        $files->GetByPeriod(
            period_id     => $period->PeriodID,
            sortby        => $sortby,
            reverseSort   => $reverseSort,
            service_id    => $serviceID,
            distinct      => 'DISTINCT',
            filter_type   => 'file_status',
            filter_value  => File::File::STATUS_OPEN(),
            filter_type2  => 'parent_file_id',
            filter_value2 => 'NULL',
            filter_type3  => 'input_conversion_rate',
            filter_value3 => 1,
            filter_type5  => 'sales_period_date_end',
            filter_value5 => $salesPeriodEndDate,
            file_type_id  => File::File::FILETYPEID_NORMAL,
            filter_string => $fliterString,
            @conversionRateCondition
        );
    } elsif ( $fileType == 0 ) {

        # we want all the open files, so just go get them.
        $files->GetByPeriod(
            period_id    => $period->PeriodID,
            sortby       => $sortby,
            reverseSort  => $reverseSort,
            service_id   => $serviceID,
            filter_type  => 'file_status',
            filter_value => File::File::STATUS_OPEN(),
            file_type_id => File::File::FILETYPEID_NORMAL
        );
    } elsif ( $fileType == 3 ) {
        my $nativeCurrency = Common::Client::Current()->Locale()->currencyFormat()->currencyCode();

        # We want all open files with 0 revenue (in the base currency).
        $files->GetByPeriod(
            period_id    => $period->PeriodID,
            sortby       => $sortby,
            reverseSort  => $reverseSort,
            service_id   => $serviceID,
            filter_type  => 'file_status',
            filter_value => File::File::STATUS_OPEN(),
            filter_string =>
'(f.revenue IS NULL OR f.revenue = 0 OR u.conversion_rate = 0 OR (f.input_conversion_rate = 1 AND u.conversion_rate IS NULL AND f.currency_code != \''
              . $nativeCurrency . '\'))',
            table_join   => qq/
                LEFT JOIN (
                    SELECT *
                    FROM user_input_conversion_rate
                    WHERE file_id NOT IN (
                        SELECT DISTINCT file_id
                        FROM user_input_conversion_rate
                        WHERE conversion_rate = 0
                    )
                ) AS u ON f.file_id = u.file_id
            /,
            file_type_id => File::File::FILETYPEID_NORMAL
        );
    } else {
        my $parentFileValue;
        if ( $fileType == 1 ) {

            # we only want 'Current' files (those without parents)
            #
            $parentFileValue = 'NULL';
        } elsif ( $fileType == 2 ) {

            # we only want 'Suspense' files (those with parents)
            #
            $parentFileValue = 'NOT NULL';
        }
        $files->GetByPeriod(
            period_id     => $period->PeriodID,
            sortby        => $sortby,
            reverseSort   => $reverseSort,
            service_id    => $serviceID,
            filter_type   => 'file_status',
            filter_value  => File::File::STATUS_OPEN(),
            filter_type2  => 'parent_file_id',
            filter_value2 => $parentFileValue,
            file_type_id  => File::File::FILETYPEID_NORMAL
        );
    }

    my $highlightFileID = $self->getParam('Highlight') || 0;

    my $count      = 0;
    my $pageFiles  = 0;
    my $lowerLimit = ( ( $pageNum - 1 ) * $pageSize ) + 1;
    my $upperLimit = $pageNum * $pageSize;

    # here's where we need to do the special sort on status
    # $statusSort == 0 means no status sort
    # $statusSort == 1 means normal status sort
    # $statusSort == 2 means reverse status sort

    if ( $statusSort == 0 ) {
        while ( my $fileObj = $files->GetNext() ) {

            # We're going to add the highlighted file to the top of the list,
            # so we need to exclude it here.
            if ( $fileObj->FileID == $highlightFileID ) {
                next();
            }

            $count++;
            if ( ( $pageSize eq 'all' ) || ( $count >= $lowerLimit && $count <= $upperLimit ) ) {
                #
                # get $fileObj xml
                $pageFiles++;
                push @{ $self->{xml}->{Files}->{OpenFiles}->{OpenFile} }, $fileObj->GetObjectXML();
            }
        }
    } else {

        # first we need to go through all of the open files and assign a SortOrder value
        my $openFilesWithSortOrder;
        while ( my $fileObj = $files->GetNext() ) {

            # We're going to add the highlighted file to the top of the list,
            # so we need to exclude it here.
            if ( $fileObj->FileID == $highlightFileID ) {
                next();
            }
            push @{$openFilesWithSortOrder}, $fileObj->GetObjectXML();
        }

        # now we need to sort the list by SortOrder
        # we'll also check the $statusSort value to see if it needs to be reversed

        if ($openFilesWithSortOrder) {
            if ( $statusSort == 1 ) {
                @{$openFilesWithSortOrder} = sort { $$b{'SortOrder'} <=> $$a{'SortOrder'} } @{$openFilesWithSortOrder};
            } else {
                @{$openFilesWithSortOrder} = sort { $$a{'SortOrder'} <=> $$b{'SortOrder'} } @{$openFilesWithSortOrder};
            }

            # and finally we need to trim it down to just what will display on this page
            #
            foreach my $sortedOpenFile ( @{$openFilesWithSortOrder} ) {
                $count++;
                if ( ( $pageSize eq 'all' ) || ( $count >= $lowerLimit && $count <= $upperLimit ) ) {
                    $pageFiles++;
                    push @{ $self->{xml}->{Files}->{OpenFiles}->{OpenFile} }, $sortedOpenFile;
                }
            }
        }
    }

    # Adding in the highlighted file if it was requested
    if ( $highlightFileID && $highlightFileID != 0 ) {

        my $highlightFile = RPS::File::File->new( file_id => $highlightFileID );

        # Let's make sure it's still an open file.
        # We don't want something wacky happening here.
        if ( $highlightFile->FileStatus() == 4 ) {
            $pageFiles++;
            $count++;
            unshift @{ $self->{xml}->{Files}->{OpenFiles}->{OpenFile} }, $highlightFile->GetObjectXML();
        }
    }

    my $lastPage;
    if ( $pageSize eq 'all' ) {
        $lastPage = 1;
    } else {
        $lastPage = ceil( $count / $pageSize );
    }

    # Let's make sure we have some files now.
    # If we don't (and we're not on the first page), let's just redirect to the last page.
    #

    if ( $pageFiles == 0 && $pageNum != 1 ) {
        $self->doFileListing( $period, $lastPage );
    } else {

        # Okay, since we've got the open files figured out,
        # let's get the new and closed files.

        $self->{xml}->{Pagination}->{TotalCount}  = $count;
        $self->{xml}->{Pagination}->{CurrentPage} = $pageNum;
        $self->{xml}->{Pagination}->{LastPage}    = $lastPage;

        # !!! We'll need an accessor to get open license income files.
        # !!! closed license income files need to appear in the regular closed section?

        $files->GetByPeriod(
            period_id    => $period->PeriodID,
            filter_type  => 'file_status',
            filter_value => File::File::STATUS_OPEN(),
            file_type_id => File::File::FILETYPE_LICENSE_INCOME
        );
        while ( my $fileObj = $files->GetNext() ) {

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{LicenseIncomeFiles}->{LicenseIncomeFile} }, $fileObj->GetObjectXML();
        }

        # !!! Wanna sort?  sort here...
        #
        # @{$self->{xml}{Files}{LicenseIncomeFiles}{LicenseIncomeFile}} = sort { $a->{OrigFileName} cmp $b->{OrigFileName} } @{$self->{xml}{Files}{LicenseIncomeFiles}{LicenseIncomeFile}};

        # get all new files for this period
        # we have to fetch these one status at a time
        #
        $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'file_status', filter_value => File::File::STATUS_NEW() );
        while ( my $fileObj = $files->GetNext() ) {

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{NewFiles}->{NewFile} }, $fileObj->GetObjectXML();
        }

        $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'file_status', filter_value => File::File::STATUS_IN_QUEUE() );
        while ( my $fileObj = $files->GetNext() ) {

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{NewFiles}->{NewFile} }, $fileObj->GetObjectXML();
        }

        $files->GetByPeriod(
            period_id    => $period->PeriodID,
            filter_type  => 'file_status',
            filter_value => File::File::STATUS_PROCESSING()
        );
        while ( my $fileObj = $files->GetNext() ) {
            my $sale = RPS::File::Sale->new();
            $fileObj->Records( $sale->GetRecordCount( $fileObj->FileID ) );

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{NewFiles}->{NewFile} }, $fileObj->GetObjectXML();
        }

        $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'file_status', filter_value => File::File::STATUS_INVALID() );
        while ( my $fileObj = $files->GetNext() ) {

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{NewFiles}->{NewFile} }, $fileObj->GetObjectXML();
        }

        # get all on-hold files for this period
        $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'file_status', filter_value => File::File::STATUS_ON_HOLD() );
        while ( my $fileObj = $files->GetNext() ) {

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{OnHoldFiles}->{OnHoldFile} }, $fileObj->GetObjectXML();
        }

        # get all closed files for this period
        #
        $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'file_status', filter_value => File::File::STATUS_CLOSED() );
        my $closed_count = 0;
        while ( my $fileObj = $files->GetNext() ) {
            $closed_count++;

            # get $fileObj xml
            push @{ $self->{xml}->{Files}->{ClosedFiles}->{ClosedFile} }, $fileObj->GetObjectXML();
        }

        my $total = $files->GetByPeriod( period_id => $period->PeriodID, filter_type => 'atomic', filter_value => 0 );
        $self->{xml}->{Files}->{UnprocessedCount} = $total - $closed_count;

        $self->{xml}->{Files}->{NeedCurrencyConversionCount} = $files->GetNumNeedCurrencyConversion();

        # we need this for later
        $self->{_closedCount} = $closed_count;
    }

}

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

    my $hFilesPrefixes = {
        ''             => 'getAllExceptionsFilename',
        'Consolidated' => 'getConsolidatedExceptionsFilename',
    };

    while ( my ( $prefix, $method ) = each %$hFilesPrefixes ) {
        my $filename = $self->$method();

        $self->{xml}->{ $prefix . 'ExceptionsFile' }->{Filename} = $filename;

        if ( -f $filename ) {
            ## file exists
            $self->{xml}->{ $prefix . 'ExceptionsFile' }->{Status} = 2;

            # Going to use the last modified date as the generation time for the report.
            my $epoch_timestamp = ( stat($filename) )[9];

            # my $timestamp = strftime( "%Y-%m-%d %H:%M:%S", localtime($epoch_timestamp) ) . " " . strftime( "%Z", localtime() );
            my $timestamp = $epoch_timestamp;

            $self->{xml}->{ $prefix . 'ExceptionsFile' }->{DateGenerated} = $timestamp;

        } elsif ( -f $filename . "-tmp" ) {
            ## file is being processed
            $self->{xml}->{ $prefix . 'ExceptionsFile' }->{Status} = 1;
        } else {
            ## file does not exist
            $self->{xml}->{ $prefix . 'ExceptionsFile' }->{Status} = 0;
        }
    }
}

sub getAllExceptionsFilename {
    my $self = shift;

    #    my $clean_name = Common::Util::clean_name($self->{xml}->{CurrentUser}->{ClientName});
    my $user       = $self->_getUser();
    my $clean_name = $user->ClientNameClean();

    #    my $clean_name = $self->_getUser()->ClientNameClean();

    my $dir = RPS::Sale::File::BaseDirectory() . "/" . $clean_name;
    `mkdir $dir` unless ( -e $dir );

    $dir .= "/exceptions";
    `mkdir $dir` unless ( -e $dir );

    my $filename = $dir . "/all_exceptions.txt";

    return $filename;
}

sub getConsolidatedExceptionsFilename {
    my $self = shift;

    my $user       = $self->_getUser();
    my $clean_name = $user->ClientNameClean();

    my $dir = RPS::Sale::File::BaseDirectory() . "/" . $clean_name;
    `mkdir $dir` unless ( -e $dir );

    $dir .= "/exceptions";
    `mkdir $dir` unless ( -e $dir );

    my $filename = $dir . "/consolidated_exceptions.xlsx";

    return $filename;
}

# This method will return either undef, or a Redirect Response
#
sub finishImport {
    my ( $self, $fileID, $forceSubmit ) = @_;

    my $file = RPS::File::File->new( file_id => $fileID );
    assert($file);
    assert( $file->FileStatus != File::File::STATUS_CLOSED );

    if ( !$file->Revenue || 0 == $file->Revenue ) {

        # Only require revenue if not a split file.
        #
        if ( !$file->ParentFileID ) {
            return RSApache::Response::Redirect->new( "/app/tracker?file=$fileID&c=revenue&redirect=" . $self->getRedirect(1) );
        }
    }

    # if there are multiple currencies and currencies with missing conversion rates,
    # we need to inject conversation rates for them
    # if _checkCurrencyConversionComplete > 0, we need to redirect to the currency conversion page,
    # otherwise, proceed to split file.
    if ( $file->CurrencyCode eq 'MUL' && $file->InputConversionRate && $self->_checkCurrencyConversionsComplete($fileID) > 0 ) {
        $self->addMessageXML( type => 'error', code => 'finish_failed' );
        return RSApache::Response::Redirect->new( "/app/tracker?file=$fileID&c=currency&redirect=" . $self->getRedirect(1) );
    }

    # if there is a foreign currency, we need to give it a conversion rate
    if ( $file->CurrencyCode ne Common::Client::Current()->Locale()->currencyFormat()->currencyCode()
        && ( !$file->InputConversionRate || !$file->GetConversionRate ) ) {
        return RSApache::Response::Redirect->new( "/app/tracker?file=$fileID&c=currency&redirect=" . $self->getRedirect(1) );
    }

    if ( $file->ParentFileID == undef && $file->RevenueChecked == 0 ) {
        $self->addMessageXML( type => 'error', code => 'finish_failed' );
        return undef;
    }

    # JPK - Before continuing, let's force the file to recalculate the number of exceptions.  This might avoid a spurious error redirect.
    #
    $file->RecalculateRemainingExceptions();

    # !!! I hate having a param check this deep in the code.
    # I've added a 'forceSubmit' flag that the qwikclose command can use
    #
    if ( $self->getParam('submit') || $forceSubmit ) {
        if ( $file->RemainingExceptions > 0 ) {

            # split the file
            my $newFile = $file->Split();
            if ($newFile) {
                $self->{xml}{NewFile} = $newFile->GetObjectXML();
            } else {
                $self->addMessageXML( type => 'error', code => 'split_failed' );
                $self->{xml}{File} = $file->GetObjectXML();
                return undef;
            }
        }

        if ( !$file->FinishImport() ) {
            $self->addMessageXML( type => 'error', code => 'finish_failed' );
        }
    }

    $self->{xml}{File} = $file->GetObjectXML();
    return undef;
}

sub _checkCurrencyConversionsComplete {
    my ( $self, $fileID ) = @_;
    my ( $sql, $sth );

    # get count of sales where the conversion_rate has not been set
    $sql = "SELECT COUNT(conversion_rate) FROM sale WHERE file_id =" . $fileID . " AND conversion_rate = 0";
    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);

    my @row = $sth->fetchrow();

    # if this value is > 0, we need to redirect to the currency conversion page,
    # otherwise, proceed to split file.
    return $row[0];
}

sub uploadFile {
    my $self = shift;
    my $fh   = shift;

    my @invalidExtensions = kInvalidFileExtensions;

    my $orig_file_name = $fh;
    $orig_file_name =~ s/.*[\/\\](.*)/$1/;

    my $ext = lc $orig_file_name;
    $ext =~ s/.*\.//;

    my ( $fileData, $is_text, $is_excel, $is_excel_xml, $is_utf16, $is_utf8 );
    $is_text      = 0;
    $is_excel     = 0;
    $is_excel_xml = 0;
    $is_utf16     = 0;
    $is_utf8      = 0;
    read( $fh, $fileData, 2, 0 );

    foreach ( split( //, $fileData ) ) {

        #print STDERR "X>".$_."<>".ord($_)."<>".sprintf("%02x",ord($_))."<X\n";
        $is_excel++ if ( sprintf( "%02x", ord($_) ) eq "d0" );
        $is_excel++ if ( sprintf( "%02x", ord($_) ) eq "cf" );
        $is_utf16++ if ( sprintf( "%02x", ord($_) ) =~ /(ff|fe)/i );
        $is_utf8++  if ( sprintf( "%02x", ord($_) ) =~ /(ef|bb)/i );
        $is_text++  if ( sprintf( "%02x", ord($_) ) =~ /(\w\w)/ ); # RSD-5339
    }
    seek( $fh, 0, 0 );

    $is_excel_xml++ if ( $fh =~ /xlsx$/ );

    # Request to block upload based on extension in case 10724
    if ( $is_excel_xml < 1 && $is_excel < 2 && $is_text < 1 && $is_utf16 < 2 && $is_utf8 < 2 || grep /^$ext$/, @invalidExtensions ) {
        die RSApache::Message->new( type => "error", code => "$orig_file_name|not_text_or_excel" );
    }

    if ( eof $fh ) {
        die RSApache::Message->new( type => "error", code => "$orig_file_name|empty" );
    }

    # this lets us reset the file handle
    my $start_of_file = tell($fh);

    # -------------------------

    # get md5sum and check for duplicate file
    my $md5_sum = Common::Util::md5sum($fh);
    if ( !defined $md5_sum ) {
        die RSApache::Message->new( type => "error", code => "$orig_file_name|unreadable" );
    }

    # reset the file handle to the start of the file
    seek( $fh, $start_of_file, 0 );

    my $files = RPS::File::Files->new();
    if ( $files->GetByOrigName( file_name => $orig_file_name ) || $files->GetByMD5( md5_sum => $md5_sum ) ) {
        die RSApache::Message->new( type => "error", code => "$orig_file_name|duplicate" );
    }

    # -------------------------

    $md5_sum = 'null'
      unless ( $self->_getUser()->AccessLevel == AppUser::DB::Item::AccessLevel::kRSAccountRep
        || $self->_getUser()->AccessLevel == AppUser::DB::Item::AccessLevel::kRSAdmin );

    my $sf          = new RPS::Sale::File();
    my $new_file_id = $sf->SimpleUploadFromWeb(
        fh        => $fh,
        filename  => $orig_file_name,
        period_id => 0,
        md5_sum   => $md5_sum,
    );
    if ($new_file_id) {
        return $new_file_id;
    } else {
        die RSApache::Message->new( type => 'error', code => "$orig_file_name|file_upload_saveerror $! $@" );
    }
}

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

    my %cookies        = CGI::Cookie->fetch;
    my $openFileCookie = $cookies{'RS_OPEN_FILES'};
    return undef unless $openFileCookie;
    return $openFileCookie->value();
}

sub _getServiceListOptions {
    my ($self, $cmd, $serviceID, $periodID) = @_;

    my $aServices = [];

    my $oAllServices;
    if ( $cmd =~ /^currency_conversion_entry$/i ) {
        $oAllServices = Client::Service::GetAllServicesNeedCurrencyConversion();
    } else {
        $oAllServices = Client::Service::GetAllServiceNamesInPeriod( periodID => $periodID );
    }

    my $includesServiceSelected = 0;
    while ( my $service = $oAllServices->next() ) {
        my %serviceHash;
        $serviceHash{serviceName} = $service->service_name;
        $serviceHash{serviceID}   = $service->service_id;

        # Setting a flag so that we'll know if the selected service actually has any open files left.
        $includesServiceSelected = 1 if $service->service_id == $serviceID;

        push @$aServices, \%serviceHash;
    }

    if ( $includesServiceSelected == 0 && $periodID == 0 ) {
        $serviceID = 0;
    }

    return $aServices, $serviceID;
}

sub _getFilterEntires {
    my ($self, $aServices, $cmd, $getJson) = @_;

    my $sql = qq/
        SELECT DISTINCT
            f.service_id,
            f.sales_period_date_end,
            DATE_FORMAT(STR_TO_DATE(f.sales_period_date_end, '%Y-%m-%d'), '%M %Y') AS date_name,
            f.currency_code,
            CASE
                WHEN SUM(CASE WHEN conversion_rate = 0 THEN 1 ELSE 0 END) > 0
                THEN 'no'
                ELSE 'yes'
            END AS conversion_rate_entered
        FROM file AS f
        INNER JOIN user_input_conversion_rate uicr ON f.file_id = uicr.file_id
        WHERE f.period_id = 0
            AND f.file_status = 4
            AND f.input_conversion_rate != 0
            AND f.parent_file_id IS NULL
            AND f.revenue != 0
        GROUP BY f.file_id
        ORDER BY f.sales_period_date_end DESC, f.service_id, f.currency_code
    /;

    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, []);
    my $aData = $sth->fetchall_arrayref({}) || [];

    my %services = map { $_->{serviceID} => $_->{serviceName} } @$aServices;

    my $hOutput = {};
    foreach my $hRow ( @$aData ) {
        if ( $hRow->{conversion_rate_entered} eq 'no' ) {
            # Conversion rate
            $hOutput->{0}{_value} = "Not Entered";
            # Slaes periods date end
            $hOutput->{0}{ $hRow->{sales_period_date_end} }{_value} = $hRow->{date_name};
            # Services
            $hOutput->{0}{ $hRow->{sales_period_date_end} }{ $hRow->{service_id} }{_value} = $services{$hRow->{service_id}};
            # Currencies per period
            $hOutput->{0}{ $hRow->{sales_period_date_end} }{0}{_value} = 'All Services';
            $hOutput->{0}{ $hRow->{sales_period_date_end} }{0}{ lc $hRow->{currency_code} }{_value} = uc $hRow->{currency_code};
            # Currencies per service
            $hOutput->{0}{ $hRow->{sales_period_date_end} }{ $hRow->{service_id} }{ lc $hRow->{currency_code} }{_value} = uc $hRow->{currency_code}
        }

        $hOutput->{1}{_value} = "All";
        $hOutput->{1}{ $hRow->{sales_period_date_end} }{_value} = $hRow->{date_name};
        $hOutput->{1}{ $hRow->{sales_period_date_end} }{ $hRow->{service_id} }{_value} = $services{$hRow->{service_id}};
        $hOutput->{1}{ $hRow->{sales_period_date_end} }{0}{_value} = 'All Services';
        $hOutput->{1}{ $hRow->{sales_period_date_end} }{0}{ lc $hRow->{currency_code} }{_value} = uc $hRow->{currency_code};
        $hOutput->{1}{ $hRow->{sales_period_date_end} }{ $hRow->{service_id} }{ lc $hRow->{currency_code} }{_value} = uc $hRow->{currency_code}
    }

    return $hOutput;
}


sub _createOpenFileSettingsSection {
    my ($self, $fileType, $pageSize, $sortby, $reverseSort, $serviceID, $aServices) = @_;

    $self->{xml}{OpenFileSettings}->{FileType}           = $fileType;
    $self->{xml}{OpenFileSettings}->{PageSize}           = $pageSize;
    $self->{xml}{OpenFileSettings}->{SortBy}             = $sortby;
    $self->{xml}{OpenFileSettings}->{ReverseSort}        = $reverseSort;
    $self->{xml}{OpenFileSettings}->{ServiceID}          = $serviceID;
    $self->{xml}{OpenFileSettings}{ServiceList}{Service} = $aServices;

    return 1;
}



1;