package RPS::File::File;

use strict;

#use warnings;
use Carp;
use Data::Dumper;

use lib '/app/tools/rps/lib';
use lib '/app/tools/raptor/lib';
use lib '/app/tools/data_classes/lib';
use Client::Service;
use Client::Client;
use Raptor::DB::Item::File;
use RPS::File::Sale;
use RPS::DB::Item::MapFaceTempMapping;
use RPS::DB::Item::MapFaceTempMappingCriteria;
use RPS::DB::Item::MapFaceTempMappingHistory;
use RPS::DB::Item::Service;
use RPS::DB::Item::SaleImportError;
use RPS::DB::Item::SaleImportErrorRawValue;
use RPS::DB::Item::SaleImportFieldMapping;
use RPS::DB::Item::SaleImportFileHeader;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Consts;
use Common::RSMath qw(round);
use Common::RSApp;
use Common::Client;
use Common::Locale;
use Common::DB::Item::MapFaceFile;
use Common::DB::Item::OrchardReport;
use RSApache::Command;

use constant DB_TABLE => "file";

#use constant FILETYPEID_NORMAL => 0;
#use constant FILETYPEID_LICENSE_INCOME => 1;
#
#use constant FILETYPE_EXCEL     =>	1;
#use constant FILETYPE_TABSEP    =>	2;
#use constant FILETYPE_PIPESEP   =>	3;
#use constant FILETYPE_COMMASEP  =>	4;
#use constant FILETYPE_LICENSE_INCOME =>	5;
#
#use constant STATUS_NEW         =>	1;
#use constant STATUS_PROCESSING  =>	2;
#use constant STATUS_INVALID     =>	3;
#use constant STATUS_OPEN        =>	4;
#use constant STATUS_CLOSED      =>	5;
#use constant STATUS_IN_QUEUE    =>	6;

# private attributes
my @attributes = qw(file_id
  service_id
  type_id
  atomic
  version_num
  file_dir
  file_name
  orig_file_name
  file_md5sum
  parent_file_id
  period_id
  file_status
  deleted
  import_pid
  records
  units
  revenue
  currency_code
  revenue_checked
  input_conversion_rate
  input_revenue
  total_exceptions
  remaining_exceptions
  notes
  physical
  input_dist_fee_pct
  user_id
  sales_period_date_begin
  sales_period_date_end
  date_created
  date_finished
  date_processed
  gross_revenue);

# what should show up in xml by default
my @xml_attributes = qw(FileID
  TypeID
  ParentFileID
  HasChild
  ServiceID
  VersionNum
  Atomic
  OrigFileName
  FileStatus
  Deleted
  Records
  Units
  Revenue
  CurrencyCode
  RevenueChecked
  InputConversionRate
  InputRevenue
  TotalExceptions
  RemainingExceptions
  Notes
  NotesSort
  Physical
  InputDistributionFee
  UserID
  SalesPeriodDateBegin
  SalesPeriodDateEnd
  DateCreated
  DateProcessed
  DateFinished
  NeedsConversionRate
  NonQualifiedErrorCount
  QualifiedErrorCount
  MapFaceStatus
  GrossRevenue
);

use base 'File::File';

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

    # initialize user properties
    foreach (@attributes) {
        $self->{$_} = undef;
    }

    # initialize user objects
    $self->{errstr} = undef;
    $self->{dirty}  = 0;

    # are we loading an existing User?
    if ( $args{file_id} ) {
        $self->Load( file_id => $args{file_id} );
        $self->{dirty} = 0;
    }
}

# --------------------------------
# Properties
# (file_id service_id type_id version_num file_dir file_name file_md5sum parent_file_id period_id file_status date_processed date_finished revenue_checked);
# --------------------------------
sub FileID {
    my $self = shift;
    return $self->{file_id};
}

sub DateProcessed {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{date_processed} ) {
            $self->{date_processed} = $rvalue;
            $self->{dirty}          = 1;
        }
    }
    return $self->{date_processed};
}

sub DateFinished {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{date_finished} ) {
            $self->{date_finished} = $rvalue;
            $self->{dirty}         = 1;
        }
    }
    return $self->{date_finished};
}

sub FileStatus {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{file_status} ) {
            $self->{file_status} = $rvalue;
            $self->{dirty}       = 1;
        }
    }
    return $self->{file_status};
}

sub FileType {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{type_id} ) {
            $self->{type_id} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{type_id};
}

sub PeriodID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{period_id} ) {
            $self->{period_id} = $rvalue;
            $self->{dirty}     = 1;
        }
    }
    return $self->{period_id};
}

sub ParentFileID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{parent_file_id} ) {
            $self->{parent_file_id} = $rvalue;
            $self->{dirty}          = 1;
        }
    }
    return $self->{parent_file_id};
}

sub MD5Sum {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{file_md5sum} ) {
            $self->{file_md5sum} = $rvalue;
            $self->{dirty}       = 1;
        }
    }
    return $self->{file_md5sum};
}

sub NonQualifiedErrorCount {
    my $self = shift;
    return RPS::DB::Item::SaleImportError->GetNonQualifiedCountByFileID( $self->FileID );
}

sub QualifiedErrorCount {
    my $self = shift;
    return RPS::DB::Item::SaleImportError->GetQualifiedCountByFileID( $self->FileID );
}

sub MapFaceStatus {
    my $self              = shift;
    my $collection        = RPS::DB::Item::MapFaceTempMapping->GetByFileID( $self->FileID );
    my $totalCount        = $collection->size;
    my $totalMapped       = 0;
    my $totalAutoApproved = 0;
    while ( $collection->hasNext() ) {
        my $mapping = $collection->next();
        my $status  = $mapping->status;
        if (   $status == RPS::DB::Item::MapFaceTempMapping::STATUS_MAPPED
            || $status == RPS::DB::Item::MapFaceTempMapping::STATUS_APPROVED
            || $status == RPS::DB::Item::MapFaceTempMapping::STATUS_AUTO_APPROVED ) {
            $totalMapped++;
        }
        if ( $status == RPS::DB::Item::MapFaceTempMapping::STATUS_AUTO_APPROVED ) {
            $totalAutoApproved++;
        }
    }

    my $status;
    if ( $totalMapped == $totalCount ) {
        $status = 'all';

        # If the only work that has been done on the file is auto-mapping,
        # we want the status to reflect that.
    } elsif ( $totalAutoApproved == $totalMapped ) {
        $status = 'none';
    } elsif ( $totalMapped > 0 ) {
        $status = 'some';
    } else {
        $status = 'none';
    }

    return $status;
}

sub NeedsConversionRate {
    my $self           = shift;
    my $nativeCurrency = Common::Client::Current()->Locale()->currencyFormat()->currencyCode();
    return undef if ( Common::Client::Current()->Locale()->currencyFormat()->currencyCode() eq $self->CurrencyCode );
    return 1 if ( Common::Client::Current()->Locale()->currencyFormat()->currencyCode() ne $self->CurrencyCode );
}

sub AdjustedRevenue {
    my $self            = shift;
    my $agg_dist_fee    = $self->GetDistFeePct;
    my $conversion_rate = $self->GetInputConversionRate;
    my $revenue         = $self->Revenue;
    $revenue *= $conversion_rate if ( $conversion_rate != 0 );
    return round( ( $revenue - ( $revenue * ( $agg_dist_fee * .01 ) ) ), 2 );
}

sub GetInputRevenue {
    my $self = shift;

    my $sql  = qq/
        SELECT sum(revenue)
        FROM user_input_revenue
        WHERE file_id = ?
        GROUP BY file_id
    /;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth  = $dbo->DoCmdWithPlaceholders( $sql, [ $self->FileID ] );
    return unless $sth->rows;
    my $response = $sth->fetchrow();

    return $response;
}

sub GetMultipleTerritoryRevenue {
    my $self = shift;

    return if $self->Physical == 1;

    my $sql = qq/
        SELECT
            associated_country,
            revenue
        FROM user_input_revenue
        WHERE file_id = ?
        ORDER BY associated_country
    /;

    if ( $self->{SetRevenue} == 1 ) {
        $sql = qq/
            SELECT
                associated_country,
                revenue,
                currency_code
            FROM user_input_revenue u, sale s
            WHERE u.file_id = ?
                AND u.file_id = s.file_id
                AND u.associated_country = s.country_code
            GROUP BY s.country_code
            ORDER BY associated_country
        /;
    }

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [ $self->FileID ] );
    return unless $sth->rows;

    my @return_data;
    while ( my $response = $sth->fetchrow_hashref() ) {
        push( @return_data, $response );
    }

    return @return_data;
}

sub GetMultipleTerritoryRevenueFromSale {
    my $self = shift;

    return if $self->Physical == 1;

    my $sql = qq/
        SELECT DISTINCT
            country_code,
            sum( price * units * conversion_rate ) AS revenue,
            currency_code
        FROM sale
        WHERE file_id = ?
            AND free = 0
        GROUP BY country_code
    /;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [ $self->FileID ] );
    return unless $sth->rows;

    my @return_data;
    while ( my $response = $sth->fetchrow_hashref() ) {
        push( @return_data, $response );
    }

    return @return_data;
}

sub GetInputConversionRate {
    my ($self, $currency) = @_;

    my ( $sql, $t_sql, $c_sql );

    if ( $currency eq "" ) {
        # skip files for which currency rates have not been set (for example, initially added entries when uploading files)
        $sql = qq/
            SELECT
                sum(revenue * conversion_rate),
                sum(revenue)
            FROM user_input_conversion_rate
            WHERE file_id = ?
                AND NOT (
                    (SELECT COUNT(1) FROM user_input_conversion_rate WHERE file_id = ? AND conversion_rate = 0)
                )
        /;
        my $csth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID, $self->FileID ]);
        return undef unless $csth->rows;
        my @rate_data = $csth->fetchrow();
        if ( $rate_data[1] != 0 ) {
            return $rate_data[0] / $rate_data[1];
        } else {
            return undef;
        }
    } else {
        # skip files for which currency rates have not been set (for example, initially added entries when uploading files)
        $sql = qq/
            SELECT conversion_rate
            FROM user_input_conversion_rate
            WHERE file_id = ?
                AND associated_currency = ?
                AND NOT (
                    (SELECT COUNT(1) FROM user_input_conversion_rate WHERE file_id = ? AND conversion_rate = 0)
                )
        /;
        my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID, $currency, $self->FileID ]);
        return undef unless $sth->rows;

        my $response = $sth->fetchrow();
        return $response;
    }

    return undef;
}

sub GetConversionRate {
    my $self = shift;

    my ( $sql, $sth );

    $sql = qq/
        SELECT
            sum(conversion_rate)
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
            AND conversion_rate > ?
    /;
    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID,  0 ]);
    return undef unless $sth->rows;

    return $sth->fetchrow();
}

sub GetDistFeePct {
    my $self   = shift;
    my $format = shift;
    my ( $sql, $asql, $bsql );
    my ( $aresponse, $bresponse );
    my $response = undef;
    if ( $format eq "" ) {
        $asql = qq/
            SELECT
                revenue,
                round(sum(revenue*(dist_fee_pct*.01)),8)
            FROM user_input_dist_fee
            WHERE 1 = 1
                AND file_id = ?
            GROUP BY user_input_dist_fee_id
        /;
        my $df_data     = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($asql, [ $self->FileID ]);
        my $return_rows = 0;
        my ( $total_revenue, $total_dist_fee );
        return undef if ( $df_data->rows == 0 );
        while ( my @df_response = $df_data->fetchrow() ) {
            $total_revenue  += $df_response[0];
            $total_dist_fee += $df_response[1];
            $return_rows++;
        }
        $response = round( ( ( $total_dist_fee / $total_revenue ) * 100 ), 8 ) if ( $return_rows > 0 && $total_revenue != 0 );
    } else {
        $sql = qq/
            SELECT dist_fee_pct
            FROM user_input_dist_fee
            WHERE 1 = 1
                AND file_id = ?
                AND associated_format = ?
        /;
        my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID, $format ]);
        return undef if ( $sth->rows == 0 );
        $response = $sth->fetchrow();
    }
    return $response;
}

sub GetDistFeePctFormats {
    my $self = shift;

    # format names are in RSCOMMON, so let's get them.
    my $dbo_c = Common::RSApp::GetCommonDB();
    my $sth   = $dbo_c->DoCmd("SELECT format_type, format_name FROM format");

    my %format_map;
    while ( my ( $format_type, $format_name ) = $sth->fetchrow_array() ) {
        $format_map{$format_type} = $format_name;
    }
    $self->{format_map} = \%format_map;
    $sth->finish();

    my $sql;
    if ( $self->InputDistFee ) {
        $sql = qq/
            SELECT
                u.associated_format,
                u.dist_fee_pct
            FROM user_input_dist_fee u
            WHERE u.file_id = ?
            ORDER BY u.associated_format
        /;
    } else {
        $sql = qq/
            SELECT DISTINCT
                sale.format_type
            FROM sale
            WHERE sale.file_id = ?
            ORDER BY sale.format_type
        /;
    }

    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders( $sql, [ $self->FileID ] );
    my ( @response, %return_data, $x );
    return undef if ( $sth->rows < 1 );

    $x = 0;
    while ( @response = $sth->fetchrow() ) {
        $response[1] =~ s/\s//g;
        $return_data{formatShort}[$x] = $response[0];
        $return_data{format}[$x]      = $format_map{ $response[0] };
        $return_data{value}[$x]       = $response[1];
        $x++;
    }

    return %return_data;
}

sub GetMultipleCurrency {
    my $self = shift;
    return undef if ( $self->Physical == 1 );

    my $sql = qq/
        SELECT
            associated_currency,
            conversion_rate,
            revenue
        FROM user_input_conversion_rate
        WHERE file_id = ?
            AND NOT (
                (SELECT COUNT(1) FROM user_input_conversion_rate WHERE file_id = ? AND conversion_rate = 0)
            )
        ORDER BY associated_currency
    /;
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders->($sql, [ $self->FileID, $self->FileID ]);
    my ( @response, %return_data, $x );
    return undef if ( $sth->rows < 1 );
    $x = 0;
    while ( @response = $sth->fetchrow() ) {
        $return_data{currency}[$x] = $response[0];
        $return_data{rate}[$x]     = $response[1];
        $return_data{revenue}[$x]  = $response[2];
        $x++;
    }
    return %return_data;
}

sub GetMultipleCurrencyFromSale {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id} || $self->FileID;

    return if ( $self->Physical == 1 );

    my $sql = <<EofSQL;
SELECT currency_code, conversion_rate, SUM(IFNULL(units*price,0) + total_revenue) AS revenue
FROM sale
WHERE file_id = ? AND free = ?
GROUP BY currency_code, conversion_rate
ORDER BY currency_code
EofSQL
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $file_id, 0 ]);
    my ( @response, %return_data, $x );
    return if ( $sth->rows < 1 );
    $x = 0;
    while ( @response = $sth->fetchrow() ) {
        $return_data{currency}[$x] = $response[0];
        $return_data{rate}[$x]     = $response[1];
        $return_data{revenue}[$x]  = $response[2];
        $x++;
    }
    return %return_data;
}

sub MultipleCurrencyRevenue {
    my $self     = shift;
    my $currency = shift;

    my $sql = qq/
        SELECT
            sum((IFNULL(units*price,0)+total_revenue)*conversion_rate)
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
            AND currency_code = ?
    /;
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID, $currency ]);
    my $response;
    return undef if ( $sth->rows == 0 );
    $response = $sth->fetchrow();
    return $response;
}

sub SalesFileDateBegin {
    my $self = shift;
    my $sql  = "SELECT min(date_begin) FROM sale WHERE file_id = ?";
    my $sth  = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID]);
    my $response;
    return undef if ( $sth->rows == 0 );
    $response = $sth->fetchrow();
    return $response;
}

sub SalesFileDateEnd {
    my $self = shift;
    my $sql  = "SELECT max(date_end) FROM sale WHERE file_id = ?";
    my $sth  = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID]);
    my $response;
    return undef if ( $sth->rows == 0 );
    $response = $sth->fetchrow();
    return $response;
}

sub ServiceID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{service_id} ) {
            $self->{service_id} = $rvalue;
            $self->{dirty}      = 1;
        }
    }
    return $self->{service_id};
}

sub TypeID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{type_id} ) {
            $self->{type_id} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{type_id};
}

sub Atomic {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{atomic} ) {
            $self->{atomic} = $rvalue;
            $self->{dirty}  = 1;
        }
    }
    return $self->{atomic};
}

sub VersionNum {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{version_num} ) {
            $self->{version_num} = $rvalue;
            $self->{dirty}       = 1;
        }
    }
    return $self->{version_num};
}

sub FileDir {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{file_dir} ) {
            $self->{file_dir} = $rvalue;
            $self->{dirty}    = 1;
        }
    }
    return $self->{file_dir};
}

sub OrigFileName {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{orig_file_name} ) {
            $self->{orig_file_name} = $rvalue;
            $self->{dirty}          = 1;
        }
    }
    return $self->{orig_file_name};
}

sub FileName {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{file_name} ) {
            $self->{file_name} = $rvalue;
            $self->{dirty}     = 1;
        }
    }
    return $self->{file_name};
}

sub Deleted {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{deleted} ) {
            $self->{deleted} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{deleted};
}

sub RevenueChecked {
    my $self = shift;

    # We're going to always return '1' here.
    # Basically, we're disabling the RevenueChecked mechanism.
    # However, we are not getting rid of the column at this time,
    # so this is a bit of a hack. JPK
    #
    return 1;

    #	# setting the value
    #	if (@_)
    #	{
    #		my ($rvalue) = @_;
    #		if($rvalue ne $self->{revenue_checked})
    #		{
    #			$self->{revenue_checked} = $rvalue;
    #			$self->{dirty} = 1;
    #		}
    #	}
    #	return $self->{revenue_checked};
}

sub ImportPID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{import_pid} ) {
            $self->{import_pid} = $rvalue;
            $self->{dirty}      = 1;
        }
    }
    return $self->{import_pid};
}

sub Records {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{records} ) {
            $self->{records} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{records};
}

sub Units {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{units} ) {
            $self->{units} = $rvalue;
            $self->{dirty} = 1;
        }
    }
    return $self->{units};
}

sub Revenue {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{revenue} ) {
            $self->{revenue} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{revenue};
}

sub DistributionFee {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{distributionfee} ) {
            $self->{distributionfee} = $dvalue;
            $self->{dirty}           = 1;
        }
    }
    return $self->{distributionfee};
}

sub CurrencyCode {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{currency_code} ) {
            $self->{currency_code} = $rvalue;
            $self->{dirty}         = 1;
        }
    }
    return $self->{currency_code};
}

sub InputConversionRate {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{input_conversion_rate} ) {
            $self->{input_conversion_rate} = $rvalue;
            $self->{dirty}                 = 1;
        }
    }
    return $self->{input_conversion_rate};
}

sub InputRevenue {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{input_revenue} ) {
            $self->{input_revenue} = $rvalue;
            $self->{dirty}         = 1;
        }
    }
    return $self->{input_revenue};
}

sub InputDistributionFee {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{input_dist_fee_pct} ) {
            $self->{input_dist_fee_pct} = $dvalue;
            $self->{dirty}              = 1;
        }
    }
    return $self->{input_dist_fee_pct};
}

sub TotalExceptions {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{total_exceptions} ) {
            $self->{total_exceptions} = $rvalue;
            $self->{dirty}            = 1;
        }
    }
    return $self->{total_exceptions};
}

sub RemainingExceptions {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{remaining_exceptions} ) {
            $self->{remaining_exceptions} = $rvalue;
            $self->{dirty}                = 1;
        }
    }
    return $self->{remaining_exceptions};
}

sub Notes {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        $rvalue =~ s/^\s*//;
        if ( $rvalue ne $self->{notes} ) {
            $self->{notes} = $rvalue;
            $self->{dirty} = 1;
        }
    }
    return $self->{notes};
}

sub NotesSort {
    my $self = shift;
    return length( $self->Notes ) ? uc( $self->Notes ) : '__';
}

sub Physical {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        $rvalue =~ s/^\s*//;
        if ( $rvalue ne $self->{physical} ) {
            $self->{physical} = $rvalue;
            $self->{dirty}    = 1;
        }
    }
    return $self->{physical};
}

sub InputDistFee {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{input_dist_fee_pct} ) {
            $self->{input_dist_fee_pct} = $dvalue;
            $self->{dirty}              = 1;
        }
    }
    return $self->{input_dist_fee_pct};
}

sub DistFeePct {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{dist_fee_pct} ) {
            $self->{dist_fee_pct} = $dvalue;
            $self->{dirty}        = 1;
        }
    }
    return $self->{dist_fee_pct};
}

sub SalesPeriodDateBegin {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{sales_period_date_begin} ) {
            $self->{sales_period_date_begin} = $dvalue;
            $self->{dirty}        = 1;
        }
    }
    return $self->{sales_period_date_begin};
}

sub SalesPeriodDateEnd {
    my $self = shift;
    if (@_) {
        my ($dvalue) = @_;
        if ( $dvalue ne $self->{sales_period_date_end} ) {
            $self->{sales_period_date_end} = $dvalue;
            $self->{dirty}        = 1;
        }
    }
    return $self->{sales_period_date_end};
}


sub HasChild {
    my $self = shift;
    my $sql  = "SELECT COUNT(*) AS count FROM file WHERE parent_file_id = ?";

    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID]);
    return undef unless ( defined $sth && $sth->rows > 0 );

    my $href = $sth->fetchrow_hashref();
    return $href->{count};
}

sub UserID {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        $rvalue =~ s/^\s*//;
        if ( $rvalue ne $self->{user_id} ) {
            $self->{user_id} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{user_id};
}

sub DateCreated {
    my $self = shift;
    $self->{date_created};
}

sub DateModified {
    my $self = shift;
    $self->{date_modified};
}

sub Error {
    my $self = shift;
    $self->{errstr};
}

sub GrossRevenue {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        if ( $rvalue ne $self->{gross_revenue} ) {
            $self->{gross_revenue} = $rvalue;
            $self->{dirty}   = 1;
        }
    }
    return $self->{gross_revenue};
}

sub GetObjectXML {

    my $self = shift;
    my %args = @_;

    # The CurrencyList XML can be very time consuming to generate.
    # Let's only do that when it's asked for explicitly.
    my $includeCurrencyList = $args{includeCurrencyList};

    # Similarly, the RelatedFileList should only be generated when we need it.
    # Actually, this seems pretty lightweight so I'm going to leave it on al lthe time (for now, at least).
    my $includeRelatedFileList = 1;

    # if the file is open or closed then we should have
    # sales data, if not, try to generate it.
    if ( $self->FileStatus == File::File::STATUS_OPEN() || $self->FileStatus == File::File::STATUS_CLOSED() ) {
        if ( !$self->Records ) {
            $self->UpdateSummary();
        }
    }

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

    my $xml = $self->SUPER::GetObjectXML(@xml_attributes);

    # set sorting order
    if ( $self->FileStatus == File::File::STATUS_OPEN ) {
        if (   ( $self->Records > $self->RemainingExceptions || $self->TotalExceptions > $self->RemainingExceptions )
            && $self->RemainingExceptions == 0
            && $self->Revenue
            && ( $self->CurrencyCode eq $nativeCurrency || $self->InputConversionRate ) ) {
            $xml->{SortOrder} = 1;
        } elsif ( ( $self->Records > $self->RemainingExceptions || $self->TotalExceptions > $self->RemainingExceptions )
            && $self->Revenue
            && ( $self->CurrencyCode eq $nativeCurrency || $self->InputConversionRate ) ) {
            $xml->{SortOrder} = 2;
        } elsif ( ( $self->Records > $self->RemainingExceptions || $self->TotalExceptions > $self->RemainingExceptions )
            && $self->Revenue ) {
            $xml->{SortOrder} = 3;
        } elsif ( $self->Records > $self->RemainingExceptions
            || $self->TotalExceptions > $self->RemainingExceptions ) {
            $xml->{SortOrder} = 4;
        } elsif ( !$self->ParentFileID ) {
            $xml->{SortOrder} = 5;
        } else {
            ## everything else
            $xml->{SortOrder} = 6;
        }
    } else {
        $xml->{SortOrder} = 0;
    }

    # set QwikClose for open files that require no more input to be closed
    # For files with non-native currency, a conversion rate must have been entered
    # (InputConversionRate=1), and the resulting converted revenue (GetInputConversionRate)
    # must be non-zero (the exception to this are split files with zero revenue).
    #
    $xml->{QwikClose} = (
              $self->FileStatus == File::File::STATUS_OPEN()
          and ( $self->Records > $self->RemainingExceptions or $self->TotalExceptions > $self->RemainingExceptions )
          and ( $self->ParentFileID or $self->RevenueChecked )
          and (
            $self->CurrencyCode eq $nativeCurrency
            or $self->InputConversionRate > 0 and ( $self->GetInputConversionRate != 0
                or ( $self->ParentFileID and 0 == $self->Revenue ) )
          )
    ) ? 1 : 0;

    # stuff we need for every file
    if ( defined $self->ServiceID ) {
        $xml->{ServiceName} = Client::Service::GetServiceName( service_id => $self->ServiceID );
    }
    $xml->{ServiceName} ||= 'Unknown';

    ( $xml->{OrigFileNamePretty} = $xml->{OrigFileName} ) =~ s/_/ /g;

    if ( $self->Revenue ) {

        # !!! I believe we want to localize the money output based on the original currency.
        # !!! So we'll need to construct a 'Locale' block that matches that, sort of.
        # !!! I say 'sort of', because we'll keep the same _numeric_ formatting.
        #
        $xml->{RevenuePretty} = _formatMoney( $self->Revenue, $self->CurrencyCode );

        if ( $self->CurrencyCode ne $nativeCurrency ) {
            if ( $self->InputConversionRate ) {
                my $conversion_rate = $self->GetInputConversionRate;
                my $revenue_native  = $self->Revenue * $conversion_rate;
                $xml->{InputConversionRate} = Common::Client::Current()->Locale()->formatNumber($conversion_rate);
                my $conversion_rate_inverse = 0;
                if ( $conversion_rate != 0 ) {
                    $conversion_rate_inverse = round( 1 / $conversion_rate * 100000000 ) / 100000000;
                }
                $xml->{InputConversionRateInverse} = Common::Client::Current()->Locale()->formatNumber($conversion_rate_inverse);
                $xml->{RevenueBasePretty}          = _formatMoney($revenue_native);
            }
        } else {
            $xml->{RevenueBasePretty} = $xml->{RevenuePretty};
        }
    } else {

        # Even if there's no revenue, we still want to display something so that we can make a link out of it.
        $xml->{RevenuePretty} = _formatMoney( 0, $self->CurrencyCode );
    }

    if ( $self->InputRevenue && $self->InputRevenue > 0 ) {
        $xml->{InputRevenuePretty} = _formatMoney( $self->GetInputRevenue );
    }

    if ( $self->InputDistFee > 0 ) {
        $xml->{AdjustedRevenue} = Common::Client::Current()->Locale()->formatMoney( $self->AdjustedRevenue );
        $xml->{DistFeePct}      = Common::Client::Current()->Locale()->formatNumber( $self->GetDistFeePct );
    } else {
        $xml->{DistFeePct} = 0;
    }

    if ( $self->InputRevenue > 0 || $self->Revenue == 0 && $self->{SetRevenue} == 1 ) {
        my ( @territory_information, $country_column, $currency_column );
        $currency_column = "currency_code";
        if ( $self->InputRevenue > 0 ) {
            @territory_information = $self->GetMultipleTerritoryRevenue;
            $country_column        = "associated_country";

            # If this is a LastFM file with more than five country codes, then the
            # input_revenue flag will be set and there will be no user_input_revenue
            # records from which to gleen the territory info.  If that's the case,
            # get the territory information from the sales. (FB15868)
            #
            if ( @territory_information == 1 && $self->ServiceID eq '402' ) {
                @territory_information = $self->GetMultipleTerritoryRevenueFromSale;
                $country_column        = "country_code";
            }
        } else {
            @territory_information = $self->GetMultipleTerritoryRevenueFromSale;
            $country_column        = "country_code";
        }
        my @country_list  = ();
        my %seen_currency = ();    # keep track of unique currency codes

        # If there are multiple territories, we'll check if there are multiple
        # currency codes.  On the UI, the user will be prompted to enter revenue
        # by currency code.
        #
        if ( @territory_information > 1 ) {
            for ( my $x = 0 ; $x < @territory_information ; $x++ ) {
                $xml->{TerritoryList}->{country}[$x]->{country_code} = $territory_information[$x]->{$country_column};
                $xml->{TerritoryList}->{country}[$x]->{revenue} =
                  Common::Client::Current()->Locale()->formatNumber( $territory_information[$x]->{revenue} );
                $xml->{TerritoryList}->{country}[$x]->{currency_code} = $territory_information[$x]->{$currency_column};
                push( @country_list, $territory_information[$x]->{$country_column} );

                $seen_currency{ $territory_information[$x]->{$currency_column} } = 1;
            }
            $xml->{TerritoryList}->{country_list} = join( " ", @country_list );

            if ( ( keys %seen_currency ) > 1 ) {
                $xml->{File}->{MultipleRevenue} = 1;
            } else {

                # If only one currency code was present across all territories, then we
                # won't need to enter revenue for each territory's currency code.
                #
                delete( $xml->{TerritoryList} );
            }
        }
    }
    if ( $self->InputDistFee > 0 || $self->{InputDistributionFee} > 0 ) {
        if ( $self->GetDistFeePctFormats ) {
            my %format_data = $self->GetDistFeePctFormats;
            my @shorts      = @{ $format_data{formatShort} };
            my @formats     = @{ $format_data{format} };
            my @values      = @{ $format_data{value} };
            if ( @values > 0 ) {
                for ( my $x = 0 ; $x < @formats ; $x++ ) {
                    $values[$x] = 0 if ( $values[$x] eq "" );
                    $xml->{DistributionFeeFormats}[$x]->{FormatShort} = $shorts[$x];
                    $xml->{DistributionFeeFormats}[$x]->{Format}      = $formats[$x];
                    $xml->{DistributionFeeFormats}[$x]->{FormatValue} = Common::Client::Current()->Locale()->formatNumber( $values[$x] );
                    $xml->{DistributionFeeFormats}[$x]{list}  = join( ",", @formats );
                    $xml->{DistributionFeeFormats}[$x]{short} = join( ",", @shorts );
                }
            } else {
                return undef;
            }
        }
    }
    if ( $self->CurrencyCode eq 'MUL' && $includeCurrencyList ) {
        my %currency_data;

        #%currency_data = $self->GetMultipleCurrency;
        %currency_data = $self->GetMultipleCurrencyFromSale if ( !defined $currency_data{currency} && $self->{ConversionInput} == 1 );
        if ( defined $currency_data{currency} ) {
            my @currencies = @{ $currency_data{currency} };
            my @rates      = @{ $currency_data{rate} };
            my @revenue    = @{ $currency_data{revenue} };
            if ( @currencies > 1 ) {
                my @combined_currency         = ();
                my $multiple_currency_revenue = 0;
                my $converted_revenue         = 0;
                my $original_revenue          = 0;
                for ( my $x = 0 ; $x < @currencies ; $x++ ) {
                    $xml->{CurrencyList}->{Currency}[$x]->{CurrencyCode} = $currencies[$x];
                    $xml->{CurrencyList}->{Currency}[$x]->{NativeRevenue} =
                      Common::Client::Current()->Locale()->formatMoney( $revenue[$x], $currencies[$x] );
                    $xml->{CurrencyList}->{Currency}[$x]->{RawRevenue} = $revenue[$x];
                    $original_revenue += $revenue[$x];
                    if ( $rates[$x] > 0 ) {
                        $multiple_currency_revenue += $self->MultipleCurrencyRevenue( $currencies[$x], $self->FileID );
                        $xml->{CurrencyList}->{Currency}[$x]->{RevenueBasePretty} = _formatMoney( $revenue[$x] * $rates[$x] );
                        $xml->{CurrencyList}->{Currency}[$x]->{Rate} =
                          Common::Client::Current()->Locale()->formatNumber( $rates[$x] );
                        $xml->{CurrencyList}->{Currency}[$x]->{USRate} = ( 1 / $rates[$x] );
                        $xml->{CurrencyList}->{Currency}[$x]->{RevenueCurrencyPretty} =
                          _formatMoney( $self->MultipleCurrencyRevenue( $currencies[$x], $self->FileID ) * ( 1 / $rates[$x] ),
                            $currencies[$x] );
                        $converted_revenue += $revenue[$x] * $rates[$x];
                    }
                    $xml->{RevenueBasePretty} = _formatMoney($converted_revenue);
                }
                if ( $self->InputConversionRate() ) {
                    $xml->{RevenuePretty} = _formatMoney($original_revenue);
                } elsif ( $multiple_currency_revenue > 0 ) {
                    $xml->{RevenuePretty} = _formatMoney($original_revenue);
                } else {
                    $xml->{RevenueBasePretty} = _formatMoney('0.00');
                }
                $xml->{CurrencyList}->{CurrencyCombined} = join( " ", @currencies );
            }
        }
    }

    if ( $includeRelatedFileList && $self->FileStatus == File::File::STATUS_OPEN() ) {
        my $childObj = $self->GetChildObj;
        my $x        = 0;
        while ($childObj) {
            $xml->{RelatedFileList}->{RelatedFile}[$x]->{FileID} = $childObj->FileID;
            $xml->{RelatedFileList}->{RelatedFile}[$x]->{PeriodID} = $childObj->PeriodID;
            ( $xml->{RelatedFileList}->{RelatedFile}[$x]->{OrigFileNamePretty} = $childObj->OrigFileName ) =~ s/_/ /g;
            $x++;
            $childObj = $childObj->GetChildObj;
        }
        my $parentObj = $self->GetParentObj;
        while ($parentObj) {
            $xml->{RelatedFileList}->{RelatedFile}[$x]->{FileID} = $parentObj->FileID;
            $xml->{RelatedFileList}->{RelatedFile}[$x]->{PeriodID} = $parentObj->PeriodID;
            ( $xml->{RelatedFileList}->{RelatedFile}[$x]->{OrigFileNamePretty} = $parentObj->OrigFileName ) =~ s/_/ /g;
            $x++;
            $parentObj = $parentObj->GetParentObj;
        }
    }

    $xml->{ConversionInput} = ( $self->{ConversionInput} == 1 ) ? 1 : 0;
    $xml->{FilePeriodDate} = Common::Client::Current()->Locale()->formatDate( $self->GetFilePeriodDate );

    $xml->{UnitsPretty}   = Common::Client::Current()->Locale()->formatNumber( $self->Units )   if ( defined $self->Units );
    $xml->{RecordsPretty} = Common::Client::Current()->Locale()->formatNumber( $self->Records ) if ( defined $self->Records );

    $xml->{TotalExceptions} = Common::Client::Current()->Locale()->formatNumber( $self->TotalExceptions )
      if ( defined $self->TotalExceptions );
    $xml->{RemainingExceptions} = Common::Client::Current()->Locale()->formatNumber( $self->RemainingExceptions )
      if ( defined $self->RemainingExceptions );

    # convert system dates to user -- AND UI -- friendly dates
    # These values contain the date and time - We just want to display the date (no time).
    #
    $xml->{DateCreatedPretty}   = Common::Client::Current()->Locale()->formatDate( $xml->{DateCreated} )   if $xml->{DateCreated};
    $xml->{DateProcessedPretty} = Common::Client::Current()->Locale()->formatDate( $xml->{DateProcessed} ) if $xml->{DateProcessed};
    $xml->{DateFinishedPretty}  = Common::Client::Current()->Locale()->formatDate( $xml->{DateFinished} )  if $xml->{DateFinished};

    return $xml;
}

# -------------------------------
# Public Methods
# -------------------------------
sub Load {
    my $self = shift;
    my %args = @_;

    my $sql;
    if ( defined $args{file_id} && $args{file_id} =~ /^\d+$/ ) {
        $sql = "SELECT * FROM " . DB_TABLE . " WHERE file_id=" . Common::RSApp::GetClientDB()->DBQuote( $args{file_id} );
    } else {
        $self->{errstr} = "file_id ($args{file_id}) not specified or not valid";
        return undef;
    }

    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    unless ( defined $sth ) {
        $self->{errstr} = "database error: " . $DBI::errstr;
        return undef;
    }

    my $href = $sth->fetchrow_hashref();
    if ( !defined $href || $sth->rows == 0 ) {
        $self->{errstr} = "no file record for file_id=$args{file_id}";
        return undef;
    }

    $self->_load($href);

    $self->{dirty} = 0;

    return 1;
}

sub Save {
    my $self = shift;

    return if ( !$self->{dirty} );

    # create sql for saving the user object
    my @set_fields;
    foreach my $attrib (@attributes) {

        # attributes to skip
        next if $attrib =~ /^(date_created|date_modified|file_id)$/;
        my $new_val = $self->{$attrib};
        next if ( !defined $new_val );

        if ( $self->{$attrib} !~ /^null$/i ) {
            $new_val = Common::RSApp::GetClientDB()->DBQuote($new_val);
        }
        push @set_fields, $attrib . "=" . $new_val;
    }

    # update or insert?
    if ( defined $self->{file_id} && $self->{file_id} =~ /^\d+$/ ) {
        my $sql = "UPDATE " . DB_TABLE . " SET " . join( ', ', @set_fields ) . " WHERE file_id=" . Common::RSApp::GetClientDB()->DBQuote( $self->{file_id} );
        my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
        if ( !defined $sth ) {
            $self->{errstr} = "update command failed: $DBI::errstr";
            return undef;
        }
    } else {
        my $sql = "INSERT INTO " . DB_TABLE . " SET date_created=NOW(), " . join( ', ', @set_fields );
        my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
        if ( !defined $sth ) {
            $self->{errstr} = "insert command failed: $DBI::errstr";
            return undef;
        } else {
            $self->{file_id} = Common::RSApp::GetClientDB()->LastInsertID;
        }
    }

    # NOTE: At this point, we *should* re-load our user object from the database to get the
    # correct values for date_modified (inserts and updates) and date_created (inserts only).
    # But for now, until we realize a need for those values after a Save, we will avoid another
    # database hit and skip grabbing the latest data. If it is decided those values are needed,
    # uncomment the following line:

    # $self->Load(file_id => $self->{file_id});

    $self->{dirty} = 0;

    return 1;
}

sub Delete {
    my $self = shift;

    # Allow deletion of any file in the open period.
    if ( $self->PeriodID == 0 ) {

        # if the file doesn't exist great,
        # otherwise remove the file from disk
        my $file;
        $file = $self->FileDir . $self->FileName;
        if ( !-e $file || ( -f $file && unlink($file) ) ) {

            # delete the associated import errors
            RPS::DB::Item::SaleImportError->DeleteByFileID( $self->FileID );

            # also delete the associated import errors raw values
            RPS::DB::Item::SaleImportErrorRawValue->DeleteByFileID( $self->FileID );

            # also delete the associated import headers
            RPS::DB::Item::SaleImportFileHeader->DeleteByFileID( $self->FileID );

            # also delete the field mappings
            RPS::DB::Item::SaleImportFieldMapping->DeleteByFileID( $self->FileID );

            # also delete the mapface file entry
            my $clientID = Common::RSApp::GetClientID();
            Common::DB::Item::MapFaceFile->DeleteByClientAndFileID( $clientID, $self->FileID );

            # and any temp mappings that may have been created already
            my $tempMappings = RPS::DB::Item::MapFaceTempMapping->GetByFileID( $self->FileID );

            while ( $tempMappings->hasNext() ) {
                my $tempMapping   = $tempMappings->next();
                my $tempMappingID = $tempMapping->mapface_temp_mapping_id;

                # delete the mapping criteria
                RPS::DB::Item::MapFaceTempMappingCriteria->DeleteByMappingID($tempMappingID);

                # Next the history
                RPS::DB::Item::MapFaceTempMappingHistory->DeleteByMappingID($tempMappingID);

                # And finally delete the mapping itself.
                $tempMapping->delete();
            }

            # delete any Orchard report references
            $clientID = Common::RSApp::GetClientID();
            Common::DB::Item::OrchardReport->DeleteByClientAndFileID( $clientID, $self->FileID );

            # delete the associated sales
            RPS::File::Sales->DeleteByFileID( dbo => Common::RSApp::GetClientDB(), file_id => $self->FileID ) || return undef;

            $self->DeleteUserInput( $self->FileID );

            # now let's delete the row from the database
            my $sql = "DELETE FROM " . DB_TABLE . " WHERE file_id = ?";
            my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID ]);
            return $sth;
        }

    }
    return undef;
}

sub DeleteUserInput {
    my $self    = shift;
    my $file_id = shift;
    my $flavor  = shift;

    my ( $sql, $sth );
    if ( $self->InputRevenue && ( $flavor eq "revenue" || $flavor eq "" ) ) {
        $sql = "DELETE FROM user_input_revenue WHERE file_id = ?";
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [$file_id]);
    }
    if ( $self->InputDistFee && ( $flavor eq "distfee" || $flavor eq "" ) ) {
        $sql = "DELETE FROM user_input_dist_fee WHERE file_id = ?";
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [$file_id]);
    }
    if ( $self->InputConversionRate && ( $flavor eq "conversion" || $flavor eq "" ) ) {
        $sql = "DELETE FROM user_input_conversion_rate WHERE file_id = ?";
        $sth = Common::RSApp::GetClientDB( Common::RSApp::GetClientDB() )->DoCmdWithPlaceholders($sql, [$file_id]);
    }

    return $sth;
}

sub UpdateRemainingExceptions {
    ## right now just being called after a (un)match
    my $self = shift;
    my $type = shift;

    # the only thing that needs updating is:
    # 	- remaining_exceptions
    # everything else (in file) is static (when a (un)match happens)

    if ( $type eq 'unmatch' ) {
        ## unmatch: increment
        $self->RemainingExceptions( $self->RemainingExceptions + 1 );
    } else {
        ## match: decrement
        $self->RemainingExceptions( $self->RemainingExceptions - 1 );
    }

    $self->Save();

}

sub RecalculateRemainingExceptions {
    my $self = shift;

    my $sql =
        "SELECT count(*) AS remaining_exceptions FROM sale WHERE file_id = ?"
      . " AND import_status!="
      . File::Sale::STATUS_MATCH
      . " AND import_status!="
      . File::Sale::STATUS_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_AUTO_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_BATCH_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_DONT_MATCH
      . " AND map_id IS NULL";

    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID ]);
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();
        $self->RemainingExceptions( $href->{remaining_exceptions} );
        $self->Save();
    }
}

sub UpdateSummary {
    my $self   = shift;
    my %args   = @_;
    my $locale = Common::Client::Current()->Locale();

    use File::Sale;

    # update all file summary info.
    # this means the following fields:
    # 	- revenue
    # 	- units
    # 	- records
    # 	- total_exceptions
    # 	- remaining_exceptions
    #   - sales_period_start_date
    #   - sales_period_end_date

    my $fileID = $self->FileID;
    my $revenueFormula;
    my $unitsFormula;
    if ( $self->Physical == 1 ) {
        $revenueFormula = 'total_revenue';
        $unitsFormula   = '(sales - returns)';
    } elsif ( $self->Physical == 2 ) {
        $revenueFormula = '(ifnull(units * price,0) + total_revenue)';
        $unitsFormula   = '(units + sales - returns)';
    } else {    # Physical == 0
        $revenueFormula = '(units * price)';
        $unitsFormula   = 'units';
    }

    my $sql = "SELECT count(*) FROM sale WHERE file_id = ? AND conversion_rate > ?";
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $fileID, 0 ]);

    my $rateSet = 0;
    $rateSet = $sth->fetchrow_arrayref()->[0] if ( $sth && $sth->rows > 0 );

    $sql = "SELECT DISTINCT(currency_code) FROM sale WHERE file_id = ? and free = ?";
    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $fileID, 0 ]);
    if ( $sth && $sth->rows > 1 ) {
        my $cCode = 'MUL';
        $self->CurrencyCode($cCode);
        $self->InputConversionRate(1);

        # add an initial entry to user_input_coversion_rate with a default rate of 0.00 for non-base currency
        if ( !$args{skip_conversion} ) {
            my %currency_data = $self->GetMultipleCurrencyFromSale( file_id => $fileID );
            if (%currency_data) {
                my @currencies = @{ $currency_data{currency} };
                my @rates      = @{ $currency_data{rate} };
                for ( my $i = 0 ; $i < @currencies ; $i++ ) {
                    $self->CreateUpdateUserInput( $fileID, 'conversion', $rates[$i], $currencies[$i] );
                }
            }
        }
    } elsif ( $sth->rows == 1 ) {
        my $cCode = $sth->fetchrow_arrayref()->[0];
        $self->CurrencyCode($cCode);
        if ( $cCode ne $locale->currencyFormat()->currencyCode() ) {
            $self->InputConversionRate(1);

            # add an initial entry to user_input_coversion_rate with a default rate of 0.00 for non-base currency
            if ( !$args{skip_conversion} ) {
                $self->CreateUpdateUserInput( $fileID, 'conversion', 0, $self->CurrencyCode );
            }
        }
    }


    if ( $self->ServiceID =~ /^(37|77|79|227|178)$/ && $self->Physical == 0 && $self->InputDistFee != 1 ) {
        $self->InputDistFee(1);
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders( "SELECT distinct format_type FROM sale WHERE file_id = ?", [$fileID] );
        while ( my $response_data = $sth->fetchrow() ) {
            $self->CreateUpdateUserInput( $fileID, 'distribution', '0.00', $response_data );
        }
    }


    my $quotedFileID = Common::RSApp::GetClientDB()->DBQuote( $fileID );
    # count all records, regardless of free flag
    $sth = Common::RSApp::GetClientDB()->DoCmd("SELECT COUNT(*) FROM sale WHERE file_id = $quotedFileID");
    my $aref = $sth->fetchrow_arrayref();
    $self->Records( $aref->[0] );

    # calculate sales_period_date_begin and sales_period_date_end
    $sth = Common::RSApp::GetClientDB()->DoCmd("SELECT MIN(date_begin), MAX(date_end) FROM sale WHERE file_id = $quotedFileID");
    $aref = $sth->fetchrow_arrayref();
    $self->SalesPeriodDateBegin( $aref->[0] );
    $self->SalesPeriodDateEnd( $aref->[1] );

    # If the client wants to skip free tracks, then we want to
    # filter those out of the units, records, and revenue totals.
    #
    my $client = new Client::Client( client_id => $self->ClientID );
    my $whereClauseA;
    if ( $client->SkipFreeTracks() ) {
        $whereClauseA = " WHERE file_id = $quotedFileID AND free = 0";
    } else {
        $whereClauseA = " WHERE file_id = $quotedFileID";
    }

    $sql = "SELECT A.total_units, A.records, A.revenue, A.gross_revenue, B.total_exceptions, C.remaining_exceptions" . " FROM"

      # A - query
      . " ("
      . " SELECT ifnull(sum($unitsFormula),0) AS total_units,"
      . " count(*) AS records,"
      . " round(ifnull(sum($revenueFormula),0), 2) AS revenue,"
      . " round(ifnull(sum(gross_revenue),0), 2) AS gross_revenue"
      . " FROM sale"
      . $whereClauseA . " ) A,"

      # B - query
      . " ( "
      . " SELECT count(*) AS total_exceptions"
      . " FROM sale"
      . " WHERE file_id = $quotedFileID"
      . " AND import_status!="
      . File::Sale::STATUS_MATCH
      . " AND import_status!="
      . File::Sale::STATUS_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_AUTO_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_BATCH_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_DONT_MATCH . " ) B,"

      # C - query
      . " ("
      . " SELECT count(*) AS remaining_exceptions"
      . " FROM sale"
      . " WHERE file_id = $quotedFileID"
      . " AND import_status!="
      . File::Sale::STATUS_MATCH
      . " AND import_status!="
      . File::Sale::STATUS_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_AUTO_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_BATCH_MAPPED
      . " AND import_status!="
      . File::Sale::STATUS_DONT_MATCH
      . " AND map_id IS NULL" . " ) C";

    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    if ( defined $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref();
        $self->Units( $href->{total_units} );

        # allow $0 revenue only if the file is a physical file, there are no records, or if the file had revenue previously
        $self->Revenue( $href->{revenue} )
          if ( $href->{revenue} != 0 || $self->Physical == 1 || $self->Records == 0 || $self->Revenue > 0 );

        $self->GrossRevenue( $href->{gross_revenue} );

        # don't continually update this field -- just the first time when the file is initialized
        $self->TotalExceptions( $href->{total_exceptions} ) if ( !defined $self->TotalExceptions );
        $self->RemainingExceptions( $href->{remaining_exceptions} );
        $self->Save() if ( !$args{no_save} );
    }
}

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

    my $fileID = $self->FileID;
    my $retval = {};

    my $sql = "SELECT COUNT(*) FROM sale WHERE file_id = ? AND import_status = ?";
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $fileID, File::Sale::STATUS_DONT_MATCH ]);
    my @tmp = $sth->fetchrow_array();
    $retval->{lines} = $tmp[0] || 0;

    my ( $units, $revenue );
    if ( $self->Physical == 1 ) {
        $units   = 'sales-returns';
        $revenue = 'total_revenue';
    } elsif ( $self->Physical == 2 ) {
        $units   = 'units+sales-returns';
        $revenue = 'ifnull(units * price,0) + total_revenue';
    } else {    # Physical == 0
        $units   = 'units';
        $revenue = 'units * price';
    }
    $sql = "SELECT SUM($units) FROM sale WHERE file_id = ? AND import_status = ?";

    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $fileID, File::Sale::STATUS_DONT_MATCH ]);
    @tmp = $sth->fetchrow_array();
    $retval->{units} = $tmp[0] || 0;

    $sql = qq/
        SELECT SUM($revenue * conversion_rate)
        FROM sale
        WHERE file_id = ? AND import_status = ?
    /;
    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $fileID, File::Sale::STATUS_DONT_MATCH ]);
    @tmp = $sth->fetchrow_array();
    $retval->{revenue} = $tmp[0] || 0;

    return $retval;
}

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

    my $retval;

    my $units;

    if ( $self->Physical == 1 ) {
        $units = 'sales-returns';
    } elsif ( $self->Physical == 2 ) {
        $units = 'units+sales-returns';
    } else {    # Physical == 0
        $units = 'units';
    }

    my $sql = "SELECT SUM($units) FROM sale WHERE file_id = ? AND free = ?";
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->FileID, 1 ]);
    my @tmp = $sth->fetchrow_array();
    $retval->{units} = $tmp[0] || 0;

    return $retval;
}

sub GetFilePeriodDate {
    my $self = shift;

    my $sql = "select start_date,end_date from period where period_id = ?";
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $self->PeriodID ]);
    my @tmp = $sth->fetchrow_array();
    return $tmp[0] . " - " . $tmp[1] if ( $tmp[1] ne "" );
    return $tmp[0] if ( $tmp[1] eq "" );
    return undef;
}

sub SetConversionRate {
    my $self = shift;
    my $rate = shift;
    return undef if ( !defined $rate );

    $self->InputConversionRate(1);
    $self->UpdateSummary( no_save => 1 );
    $self->Save();
    $self->CreateUpdateUserInput( $self->FileID, 'conversion', $rate, '' );

    # update all sale records, too
    return RPS::File::Sale->SetConversionRate( dbo => Common::RSApp::GetClientDB(), file_id => $self->FileID, rate => $rate );
}

sub SetMultipleConversionRate {
    my $self     = shift;
    my $currency = shift;
    my $rate     = shift;
    return undef if ( !defined $rate && !defined $currency );

    # This method is extremely expensive and takes a lot of time but provides no benefit.
    # I'm disabling it as part of the RSD-11843 task, but keeping it in the code for now.
    # let's make sure the file stats are up to date
    # $self->UpdateSummary( no_save => 1 );

    # set rate for the file object
    $self->InputConversionRate(1);
    $self->Save();
    $self->CreateUpdateUserInput( $self->FileID, 'conversion', $rate, $currency );

    # update all sale records, too
    return RPS::File::Sale->SetMultipleConversionRate(
        dbo      => Common::RSApp::GetClientDB(),
        file_id  => $self->FileID,
        rate     => $rate,
        currency => $currency
    );
}

sub UpdateCurrencyConversionRevenue {
    my $self         = shift;
    my $fileID       = shift;
    my $currencyCode = shift;
    my $revenue      = shift;
    return undef if ( !defined $fileID || !defined $currencyCode || !defined $revenue );

    # We only need to make this update if the entries already exist in the table.
    #
    my $querySql = "SELECT COUNT(*) FROM user_input_conversion_rate WHERE file_id = ? AND associated_currency = ?";
    my $sth      = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($querySql, [ $fileID, $currencyCode ]);
    my $exists   = $sth->fetchrow();
    if ( $exists > 0 ) {
        my $updateSql =
          "UPDATE user_input_conversion_rate SET revenue = $revenue WHERE file_id = ? AND associated_currency = ?";
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($updateSql, [ $fileID, $currencyCode ]);
    }
    return 1;
}

sub SetRevenue {
    my $self             = shift;
    my $revenue          = shift || return undef;
    my $skipRevenueFlag  = shift;
    my $negative_revenue = 0;

    $negative_revenue = 1 if ( $revenue < 0 );

    # let's make sure the file stats are up to date
    $self->UpdateSummary( no_save => 1 );

    # set revenue for the file object
    $self->Revenue($revenue);
    $self->InputRevenue(1);
    $self->Save();

    my $unit_revenue = $revenue / $self->Units;
    my $client       = new Client::Client( client_id => $self->ClientID );
    my $skipFreeFlag = $client->SkipFreeTracks();

    $self->CreateUpdateUserInput( $self->FileID, 'revenue', $revenue, '' ) if ( !$skipRevenueFlag );

    return RPS::File::Sale->SetRevenue(
        dbo            => Common::RSApp::GetClientDB(),
        file_id        => $self->FileID,
        revenue        => $unit_revenue,
        skip_free_flag => $skipFreeFlag
    );
}

sub SetMultipleTerritoryRevenue {
    my $self      = shift;
    my $revenue   = shift || return undef;
    my $file_id   = shift || return undef;
    my $territory = shift || return undef;

    my $first_reset = shift;

    # let's make sure the file stats are up to date
    $self->UpdateSummary( no_save => 1 );
    $self->InputRevenue(1);

    # update all sale records, too
    my $client = new Client::Client( client_id => $self->ClientID );
    my $skipFreeFlag = $client->SkipFreeTracks();
    $self->CreateUpdateUserInput( $file_id, 'revenue', $revenue, $territory );

    return RPS::File::Sale->SetMultipleTerritoryRevenue(
        dbo            => Common::RSApp::GetClientDB(),
        file_id        => $file_id,
        revenue        => $revenue,
        skip_free_flag => $skipFreeFlag,
        territory      => $territory,
        first_reset    => $first_reset
    );
}

sub SetDistributionFee {
    my $self             = shift;
    my $distribution_fee = shift;
    my $format           = shift;
    $self->InputDistributionFee(1);
    $self->Save();
    $self->CreateUpdateUserInput( $self->FileID, 'distribution', $distribution_fee, $format );

    return 1;
}

sub CreateUpdateUserInput {
    my $self             = shift;
    my $file_id          = shift;
    my $flavor           = shift;
    my $key_value        = shift;
    my $associated_value = shift;
    my %pieces           = (
        revenue => {
            table            => 'user_input_revenue',
            associated_field => 'associated_country',
            key_field        => 'revenue',
            initial_field    => 'country_code',
        },
        conversion => {
            table            => 'user_input_conversion_rate',
            associated_field => 'associated_currency',
            key_field        => 'conversion_rate',
            initial_field    => 'currency_code'
        },
        distribution => {
            table            => 'user_input_dist_fee',
            associated_field => 'associated_format',
            key_field        => 'dist_fee_pct',
            initial_field    => 'format_type',
        },
    );
    my ( $querySql, $insertSql, $updateSql, $sth, @tmp, $thisSql, $raw_revenue );

    if ( !$associated_value || $associated_value eq "" ) {
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders(
            "select distinct $pieces{$flavor}{initial_field} from sale where file_id = ? order by 1",
            [ $file_id ]
        );
        $associated_value = $sth->fetchrow();
    }

    if ( $flavor ne "revenue" ) {
        my $sql = qq/
            SELECT sum(units*price)
            FROM sale
            WHERE file_id = ? AND $pieces{$flavor}{initial_field} = ?
        /;
        $sql = qq/
            SELECT sum(total_revenue)
            FROM sale
            WHERE file_id = ? AND $pieces{$flavor}{initial_field} = ?
        / if ( $self->Physical == 1 );
        $sql = qq/
            SELECT sum(ifnull(units*price,0) + total_revenue)
            FROM sale
            WHERE file_id = ? AND $pieces{$flavor}{initial_field} = ?
        / if ( $self->Physical == 2 );
        $sth         = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $file_id, $associated_value ]);
        $raw_revenue = $sth->fetchrow();
    }

    my $gross_revenue;
    if ( $flavor eq 'conversion' ) {
        my $sql = qq/
            SELECT sum(gross_revenue)
            FROM sale
            WHERE file_id = ? AND $pieces{$flavor}{initial_field} = ?
        /;
        $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $file_id, $associated_value ]);
        $gross_revenue = $sth->fetchrow();
    }

    $querySql = qq/
        SELECT count(*)
        FROM $pieces{$flavor}{table}
        WHERE file_id = ? AND $pieces{$flavor}{associated_field} = ?
    /;
    $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($querySql, [ $file_id, $associated_value ]);
    @tmp = $sth->fetchrow_array();

    # INSERT
    my @insertFields = (
        'file_id',
        $pieces{$flavor}{associated_field},
        $pieces{$flavor}{key_field}
    );

    my @inserValues = (
        $file_id,
        $associated_value,
        $key_value
    );

    if ( $flavor ne 'revenue' ) {
        push @insertFields, 'revenue';
        push @inserValues, $raw_revenue;
    }

    if ( $flavor eq 'conversion' ) {
        push @insertFields, 'gross_revenue';
        push @inserValues, $gross_revenue;
    }

    $insertSql = "INSERT INTO $pieces{$flavor}{table} ("
        . join(',', @insertFields)
        . ") VALUES ("
        . join(',', map {'?'} @insertFields)
        . ")";

    if ( $tmp[0] == 0 ) {
        Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($insertSql, \@inserValues);
        return 1;
    }

    # UPDATE
    my @updateValues = ($key_value);
    push @updateValues, $raw_revenue if $flavor ne 'revenue';
    push @updateValues, $gross_revenue if $flavor eq 'conversion';
    push @updateValues, ($file_id, $associated_value);

    my $revenueField = $flavor ne 'revenue' ? ', revenue = ?' : '';
    $revenueField .= ', gross_revenue = ?' if $flavor eq 'conversion';

    $updateSql = qq/
        UPDATE $pieces{$flavor}{table}
        SET $pieces{$flavor}{key_field} = ? $revenueField
        WHERE file_id = ? AND $pieces{$flavor}{associated_field} = ?
    /;

    Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($updateSql, \@updateValues);
    return 1;
}

sub Clone {
    my $self = shift;
    my $new_file = RPS::File::File->new( dbo => Common::RSApp::GetClientDB() );

    my @dont_clone = qw(file_id
      import_pid
      file_md5sum
      date_created
      date_modified
      input_revenue
      units
      revenue
      records
      total_exceptions
      remaining_exceptions
    );

    foreach my $field (@attributes) {
        next if ( scalar( grep /$field/, @dont_clone ) > 0 );

        $new_file->{$field} = $self->{$field};
    }

    return $new_file;
}

sub FinishImport {
    my $self  = shift;
    my $today = Common::Util::today_and_now();

    # !!! All this does is set the status to CLOSED
    # !!! We are proposing a sanity-check here to make SURE
    # !!! there are no exceptions left.
    # !!! Apparently we cannot trust the 'remaining_exceptions' column...
    # !!! So, what to do?  Should we scan the sale table here and count up sales with STATUS_NO_MATCH?
    $self->RecalculateRemainingExceptions();
    if ( 0 != $self->RemainingExceptions() ) {
        return undef;
    }

    $self->FileStatus(File::File::STATUS_CLOSED);
    $self->DateFinished($today);
    $self->Save();

    return 1;
}

sub GetParentObj {
    my $self = shift;
    return undef unless $self->ParentFileID;

    my $class = ref($self);
    return $class->new( dbo => Common::RSApp::GetClientDB(), file_id => $self->ParentFileID );
}

sub GetChildObj {
    my $self = shift;

    my $sql = 'SELECT * FROM ' . DB_TABLE . ' WHERE parent_file_id = ?';
    my $sth = Common::RSApp::GetClientDB()->DoCmdWithPlaceholders( $sql, [ $self->FileID ] );

    return undef unless $sth->rows;

    my $class = ref($self);
    my $childObj = $class->new( dbo => Common::RSApp::GetClientDB() );
    $childObj->_load( $sth->fetchrow_hashref );

    return $childObj;
}

sub Split {
    my $self = shift;

    # usually can't split a file that has no valid records
    if ( $self->Records == 0 || $self->RemainingExceptions == $self->Records ) {

        # special case: allow a file that hasn't ever been split, i.e. no parent (force split)
        return undef if ( $self->ParentFileID );    ## child, so can't split
    }

    my $new_file = $self->Clone();
    my $tmp_name = $self->OrigFileName;

    # create the split names
    my ( $new_name1, $new_name2 );

    # if it already has a split extension
    if ( ( $tmp_name =~ /^(.*)-([a-z]+)(\.\w+)?$/ ) && ( $self->ParentFileID ) ) {

        # incrementing 'a' returns 'b'
        # incrementing 'z' returns 'aa'
        # incrementing 'aa' returns 'ab'
        # and so on...
        my $next = lc $2;
        $next++;
        $new_name2 = "$1-$next$3";
    }

    # if it has a file extension
    elsif ( $tmp_name =~ /^(.*)\.(\w+)$/ ) {
        $new_name1 = "$1-a.$2";
        $self->OrigFileName($new_name1);

        $new_name2 = "$1-b.$2";
    }

    # no extension
    else {
        $new_name1 = $tmp_name . '-a';
        $self->OrigFileName($new_name1);

        $new_name2 = $tmp_name . '-b';
    }
    $new_file->OrigFileName($new_name2);

    $new_file->ParentFileID( $self->FileID );
    $new_file->Save() || return undef;

    # update sales records with new file_id
    my $success = RPS::File::Sale->SplitFile(
        dbo => Common::RSApp::GetClientDB(),
        file_id => $self->FileID,
        new_file_id => $new_file->FileID
    );

    if ($success) {

        $new_file->UpdateSummary( skip_conversion => 1, );

        if ( $self->InputDistFee ) {
            my %distFee = $self->GetDistFeePctFormats();
            my @shorts  = @{ $distFee{formatShort} };
            my @values  = @{ $distFee{value} };
            my %format;
            for ( my $x = 0 ; $x < @shorts ; $x++ ) {
                $format{ $shorts[$x] } = $values[$x];
            }
            $self->DeleteUserInput( $self->FileID, 'distfee' );
            $new_file->DeleteUserInput( $new_file->FileID, 'distfee' );
            $self->InputDistFee(0);
            $new_file->InputDistFee(0);
            %distFee = $self->GetDistFeePctFormats();
            @shorts  = @{ $distFee{formatShort} };
            @values  = @{ $distFee{value} };

            if ( @values > 0 ) {
                for ( my $x = 0 ; $x < @shorts ; $x++ ) {
                    $self->CreateUpdateUserInput( $self->FileID, 'distribution', $format{ $shorts[$x] }, $shorts[$x] );
                }
                $self->InputDistFee(1);
            }
            %distFee = $new_file->GetDistFeePctFormats();
            @values  = @{ $distFee{value} };
            @shorts  = @{ $distFee{formatShort} };
            if ( @values > 0 ) {
                for ( my $x = 0 ; $x < @shorts ; $x++ ) {
                    $new_file->CreateUpdateUserInput( $new_file->FileID, 'distribution', $format{ $shorts[$x] }, $shorts[$x] );
                }
                $new_file->InputDistFee(1);
            }
        }

        if ( $self->InputConversionRate ) {
            if ( $self->CurrencyCode eq 'MUL' ) {

                $self->DeleteUserInput( $self->FileID, 'conversion' );

                my %currency_data = $self->GetMultipleCurrencyFromSale( file_id => $self->FileID );

                if (%currency_data) {
                    my @currencies = @{ $currency_data{currency} };
                    my @rates      = @{ $currency_data{rate} };
                    for ( my $x = 0 ; $x < @currencies ; $x++ ) {
                        $self->CreateUpdateUserInput( $self->FileID, 'conversion', $rates[$x], $currencies[$x] ) if ( $rates[$x] > 0 );
                    }
                }

                if ( %currency_data && scalar @{ $currency_data{currency} } == 1 )    # must be only 1 currency
                {
                    $self->CurrencyCode( $currency_data{currency}[0] );
                }

                %currency_data = $new_file->GetMultipleCurrencyFromSale( file_id => $new_file->FileID );
                if (%currency_data) {
                    my @currencies = @{ $currency_data{currency} };
                    my @rates      = @{ $currency_data{rate} };
                    for ( my $x = 0 ; $x < @currencies ; $x++ ) {
                        $new_file->CreateUpdateUserInput( $new_file->FileID, 'conversion', $rates[$x], $currencies[$x] )
                          if ( $rates[$x] > 0 );
                    }
                }

                if ( %currency_data && scalar @{ $currency_data{currency} } == 1 )    # must be only 1 currency
                {
                    $new_file->CurrencyCode( $currency_data{currency}[0] );
                }
            } else {
                $new_file->CreateUpdateUserInput( $new_file->FileID, 'conversion', $self->GetInputConversionRate, $self->CurrencyCode );
            }
            $new_file->InputConversionRate(1);
        }

        $new_file->Save();

        $self->UpdateSummary( skip_conversion => 1, );

        $self->TotalExceptions( $self->TotalExceptions - $new_file->TotalExceptions );
        $self->Save();

        return $new_file;
    } else {
        return undef;
    }
}

sub ReopenFile {
    my $self = shift;
    return undef unless ( $self->PeriodID == 0 && $self->FileStatus == File::File::STATUS_CLOSED );

    $self->FileStatus(File::File::STATUS_OPEN);
    $self->Save();
}

sub GetExceptionsFile {
    my $self = shift;

    # setup the files and directories
    my $client = new Client::Client( client_id => $self->ClientID );
    my $client_name = Common::Util::clean_name( $client->ClientName );

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

    my $file_dir = "/app/shared/sale_import/$client_name";
    if ( !-e $file_dir ) {
        `mkdir $file_dir`;
    }

    $file_dir = $file_dir . "/exceptions";
    if ( !-e $file_dir ) {
        `mkdir $file_dir`;
    }

    my $serviceName = Client::Service::GetServiceName( service_id => $self->ServiceID );
    $serviceName =~ s/\s+/_/g;
    $serviceName =~ s/\W+//g;
    my $origName = $self->OrigFileName();
    $origName =~ s/\.\w+$//;
    my $final_file = "$file_dir/EXC-$serviceName-$origName.txt";

    # if(-e $final_file)
    # {
    # return $final_file;
    # }

    open( OUT, "> $final_file" ) or return undef;

    my @fields = qw(sale_id
      file_id
      product_id
      product_type
      format_type
      media_type
      date_begin
      date_end
      service_product_id
      client_product_id
      upc
      isrc
      artist_name
      album_name
      track_name
      label_name
      track_num
      gross_units
      returns
      net_units
      price
      total_revenue
      currency_code
      conversion_rate
      country_code
      line_num
    );

    push( @fields, $extPriceColumn );

    # the header row
    # album-id and track-id will not have data, they
    # are just col. headings so that someone can fill
    # that data in later after the file has been downloaded.
    print OUT join( "\t", @fields, "album-id", "track-id" ) . "\n";

    # !!! Something is clobbering the @INC path _occasionally_, which causes the
    # call to new File::Sales() to fail.  So these next two lines are a hack to try
    # and patch over the problem...
    #
    #    use lib '/app/tools/data_classes/lib';
    #    require 'File::Sale';
    #    require '/app/tools/data_classes/lib/File/Sale.pm';

    # get the data
    #	my $coll = new File::Sales();
    my $coll = RPS::File::Sales->new();
    $coll->GetUnmatchedByFile( file_id => $self->FileID );
    while ( my $sale = $coll->GetNext() ) {

        #Common::Log::Print("Sale record: " . Dumper($sale));
        # quick calculation
        my $ext_price;
        if (
            $self->Physical == 1
            || (   $self->Physical == 2
                && $sale->{product_type} ne RPS::File::Sale->TYPE_ALBUM
                && $sale->{product_type} ne RPS::File::Sale->TYPE_TRACK )
          ) {
            $sale->{gross_units} = $sale->{sales};
            $sale->{net_units}   = $sale->{sales} - $sale->{returns};
            $sale->{price}       = '';

            #------------------ total_revenue -------- conversion_rate
            $ext_price = $sale->{ $fields[21] } * $sale->{ $fields[23] };
        } else {
            $sale->{returns}       = '';
            $sale->{net_units}     = $sale->{units};
            $sale->{total_revenue} = $sale->{units} * $sale->{price};

            #-------------------- units ---------------- price ------------- conversion_rate
            $ext_price = $sale->{ $fields[19] } * $sale->{ $fields[20] } * $sale->{ $fields[23] };
        }
        $sale->{$extPriceColumn} = $ext_price;

        # quick formatting
        $sale->{date_begin} = Common::Client::Current()->Locale()->formatDate( $sale->{date_begin} );
        $sale->{date_end}   = Common::Client::Current()->Locale()->formatDate( $sale->{date_end} );

        print OUT join( "\t", map { $sale->{$_} } @fields ) . "\n";
    }
    close(OUT);

    return $final_file;
}

sub GetErrorLog {
    my $self = shift;

    # This report will consist of either all of the qualifying errors or all of the non-qualifying errors.
    # We can use the file status to determine which we should grab.
    my $error_type;
    if ( $self->FileStatus() == Raptor::DB::Item::File::STATUS_INVALID ) {
        $error_type = 'non';
    } elsif ( $self->FileStatus() == Raptor::DB::Item::File::STATUS_ON_HOLD ) {
        $error_type = 'qualifying';
    } else {
        die "Cannot generate error log for this file\n";
    }

    # setup the files and directories
    my $client = new Client::Client( client_id => $self->ClientID );
    my $client_name = Common::Util::clean_name( $client->ClientName );

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

    my $file_dir = "/app/shared/sale_import/$client_name";
    if ( !-e $file_dir ) {
        `mkdir $file_dir`;
    }

    $file_dir = $file_dir . "/error_log";
    if ( !-e $file_dir ) {
        `mkdir $file_dir`;
    }

    my $serviceName = Client::Service::GetServiceName( service_id => $self->ServiceID );
    $serviceName =~ s/\s+/_/g;
    $serviceName =~ s/\W+//g;
    my $origName = $self->OrigFileName();
    $origName =~ s/\.\w+$//;
    my $final_file = "$file_dir/";

    if ( $error_type eq 'non' ) {
        $final_file .= "NON_";
    }

    my $fileID = $self->FileID();

    $final_file .= "QUALIFYING_ERRORS-$serviceName-$origName-$fileID.txt";

    # If we've already generated it, let's just serve that up.
    if ( -e $final_file ) {
        return $final_file;
    }

    # We have to refer to some of the fields by their place in the array later,
    # so we need to keep track of how many extra fields we're adding in this time.
    my $extraFields = 0;

    # If you want a new mapface column to show up in the file, start here.
    my $fileRetailer;
    my $fileProductType;
    my $fileFormatType;
    my $fileMediaType;
    my $fileCountry;
    my $fileAssetType;
    my $fileSalesType;
    my $fileType;
    my $fileMediaCode;
    my $fileDeliveryType;
    my $fileDeliveryFormat;

    my %fieldToHeader;

    my $fileHeaders = RPS::DB::Item::SaleImportFileHeader->GetAllByFileID( $self->FileID );
    while ( $fileHeaders->hasNext() ) {
        my $fileHeader = $fileHeaders->next();

        # This is the field name from the field map for the importer.
        my $field = $fileHeader->field;

        # And this is the header used in the file for that column.
        my $header = $fileHeader->header;

        if ($header) {

            # So this is the list of "file_" fields that we support in MapFace so far.
            # This will need to be updated any time support is added for a new one.
            # It will also need to be added to the @fields array below at the appropriate spot.
            if ( $field eq 'serviceID' ) {
                if ($fileRetailer) {
                    $fileRetailer .= " / ";
                }
                $fileRetailer .= $header;
                $header = $fileRetailer;
            } elsif ( $field eq 'countryCode' ) {
                if ($fileCountry) {
                    $fileCountry .= " / ";
                }
                $fileCountry .= $header;
                $header = $fileCountry;
            } elsif ( $field eq 'productType' ) {
                if ($fileProductType) {
                    $fileProductType .= " / ";
                }
                $fileProductType .= $header;
                $header = $fileProductType;
            } elsif ( $field eq 'formatType' ) {
                if ($fileFormatType) {
                    $fileFormatType .= " / ";
                }
                $fileFormatType .= $header;
                $header = $fileFormatType;
            } elsif ( $field eq 'mediaType' ) {
                if ($fileMediaType) {
                    $fileMediaType .= " / ";
                }
                $fileMediaType .= $header;
                $header = $fileMediaType;
            } elsif ( $field eq 'assetType' ) {
                if ($fileAssetType) {
                    $fileAssetType .= " / ";
                }
                $fileAssetType .= $header;
                $header = $fileAssetType;
            } elsif ( $field eq 'salesType' ) {
                if ($fileSalesType) {
                    $fileSalesType .= " / ";
                }
                $fileSalesType .= $header;
                $header = $fileSalesType;
            } elsif ( $field eq 'type' ) {
                if ($fileType) {
                    $fileType .= " / ";
                }
                $fileType .= $header;
                $header = $fileType;
            } elsif ( $field eq 'mediaCode' ) {
                if ($fileMediaCode) {
                    $fileMediaCode .= " / ";
                }
                $fileMediaCode .= $header;
                $header = $fileMediaCode;
            } elsif ( $field eq 'deliveryType' ) {
                if ($fileDeliveryType) {
                    $fileDeliveryType .= " / ";
                }
                $fileDeliveryType .= $header;
                $header = $fileDeliveryType;
            } elsif ( $field eq 'deliveryFormat' ) {
                if ($fileDeliveryFormat) {
                    $fileDeliveryFormat .= " / ";
                }
                $fileDeliveryFormat .= $header;
                $header = $fileDeliveryFormat;
            }

            # Let's save this mapping for when we need to spit out the raw values for the sales.
            $fieldToHeader{$field} = $header;
        }
    }

    open( OUT, "> $final_file" ) or return undef;

    my @fields = ( 'sale_id', 'file_id' );

    if ($fileRetailer) {
        push( @fields, $fileRetailer );
        $extraFields++;
    }

    push( @fields, 'retailer' );
    push( @fields, 'product_id' );

    if ($fileProductType) {
        push( @fields, $fileProductType );
        $extraFields++;
    }
    if ($fileFormatType) {
        push( @fields, $fileFormatType );
        $extraFields++;
    }
    if ($fileMediaType) {
        push( @fields, $fileMediaType );
        $extraFields++;
    }
    if ($fileAssetType) {
        push( @fields, $fileAssetType );
        $extraFields++;
    }
    if ($fileSalesType) {
        push( @fields, $fileSalesType );
        $extraFields++;
    }
    if ($fileType) {
        push( @fields, $fileType );
        $extraFields++;
    }
    if ($fileMediaCode) {
        push( @fields, $fileMediaCode );
        $extraFields++;
    }
    if ($fileDeliveryType) {
        push( @fields, $fileDeliveryType );
        $extraFields++;
    }
    if ($fileDeliveryFormat) {
        push( @fields, $fileDeliveryFormat );
        $extraFields++;
    }

    push( @fields, 'product_type' );
    push( @fields, 'format_type' );
    push( @fields, 'media_type' );
    push( @fields, 'date_begin' );
    push( @fields, 'date_end' );
    push( @fields, 'service_product_id' );
    push( @fields, 'client_product_id' );
    push( @fields, 'upc' );
    push( @fields, 'isrc' );
    push( @fields, 'artist_name' );
    push( @fields, 'album_name' );
    push( @fields, 'track_name' );
    push( @fields, 'label_name' );
    push( @fields, 'track_num' );
    push( @fields, 'gross_units' );
    push( @fields, 'returns' );
    push( @fields, 'net_units' );
    push( @fields, 'price' );
    push( @fields, 'total_revenue' );
    push( @fields, 'currency_code' );
    push( @fields, 'conversion_rate' );

    if ($fileCountry) {
        push( @fields, $fileCountry );

        # Extra fields don't matter at this point, for our purposes below anyway.
        #$extraFields++;
    }

    push( @fields, 'country_code' );
    push( @fields, 'line_num' );
    push( @fields, $extPriceColumn );

    # the header row
    # album-id and track-id will not have data, they
    # are just col. headings so that someone can fill
    # that data in later after the file has been downloaded.
    push( @fields, 'album-id' );
    push( @fields, 'track-id' );
    push( @fields, 'error' );

    print OUT join( "\t", @fields ) . "\n";

    # We only want one line per sale (and not one line per error)
    # so we'll grab the sales now and then fetch all of the qualifying errors for them after.
    my $coll = RPS::File::Sales->new();

    if ( $error_type eq 'qualifying' ) {
        $coll->GetSalesWithQualifyingErrors( file_id => $self->FileID );
    } else {
        $coll->GetSalesWithNonQualifyingErrors( file_id => $self->FileID );
    }

    while ( my $sale = $coll->GetNext() ) {

        # !!! This stuff with the field reference numbers will need to be updated !!!

        #Common::Log::Print("Sale record: " . Dumper($sale));
        # quick calculation
        my $ext_price;
        if (
            $self->Physical == 1
            || (   $self->Physical == 2
                && $sale->{product_type} ne RPS::File::Sale->TYPE_ALBUM
                && $sale->{product_type} ne RPS::File::Sale->TYPE_TRACK )
          ) {
            $sale->{gross_units} = $sale->{sales};
            $sale->{net_units}   = $sale->{sales} - $sale->{returns};
            $sale->{price}       = '';

            #------------------ total_revenue -------- conversion_rate
            $ext_price = $sale->{ $fields[ 22 + $extraFields ] } * $sale->{ $fields[ 24 + $extraFields ] };
        } else {
            $sale->{returns}       = '';
            $sale->{net_units}     = $sale->{units};
            $sale->{total_revenue} = $sale->{units} * $sale->{price};

            #-------------------- units ---------------- price ------------- conversion_rate
            $ext_price =
              $sale->{ $fields[ 20 + $extraFields ] } * $sale->{ $fields[ 21 + $extraFields ] } * $sale->{ $fields[ 24 + $extraFields ] };
        }
        $sale->{$extPriceColumn} = $ext_price;

        if ( $sale->{service_id} ) {
            my $service = RPS::DB::Item::Service->Lookup( service_id => $sale->{service_id} );
            $sale->{retailer} = $service->service_name;
        }

        if ( $sale->{media_type} == 0 ) {
            $sale->{media_type} = '';
        }

        # quick formatting
        $sale->{date_begin} = Common::Client::Current()->Locale()->formatDate( $sale->{date_begin} );
        $sale->{date_end}   = Common::Client::Current()->Locale()->formatDate( $sale->{date_end} );

        # And now to populate the raw values from the file for some of the fields.
        my $rawValues = RPS::DB::Item::SaleImportErrorRawValue->GetAllBySaleID( $sale->{sale_id} );
        while ( $rawValues->hasNext() ) {
            my $rawValue = $rawValues->next();
            if ( $rawValue->value ) {
                my $headerName = $fieldToHeader{ $rawValue->field };
                $sale->{$headerName} = $rawValue->value;
            }
        }

        my %distinctErrors;
        if ( $error_type eq 'non' ) {
            my $importErrors = RPS::DB::Item::SaleImportError->GetAllNonqualifyingBySaleID( $sale->{sale_id} );
            while ( $importErrors->hasNext() ) {
                my $importError = $importErrors->next();
                $distinctErrors{ $importError->description } = 1;
            }
        } elsif ( $error_type eq 'qualifying' ) {
            my $importErrors = RPS::DB::Item::SaleImportError->GetAllQualifyingBySaleID( $sale->{sale_id} );
            while ( $importErrors->hasNext() ) {
                my $importError = $importErrors->next();
                if ( $importError->mapped eq 'N' ) {
                    $distinctErrors{ $importError->description } = 1;
                }
            }
        }

        my @errors;

        foreach my $distinctError ( keys %distinctErrors ) {
            push( @errors, $distinctError );
        }

        $sale->{error} = join( "; ", @errors );

        print OUT join( "\t", map { $sale->{$_} } @fields ) . "\n";
    }
    close(OUT);

    return $final_file;
}

# -------------------------------
# Private Methods
# -------------------------------
sub _load {
    my $self = shift;
    my $href = shift;

    map { $self->{$_} = $href->{$_} } @attributes;
}

sub _formatMoney {
    my ( $money, $currency ) = @_;

    my %format;
    $format{value} = Common::Client::Current()->Locale()->formatNumber( $money, '0.2' );
    if ($currency) {
        $format{symbol} = Common::CurrencyFormat::Symbol($currency);
    } else {
        $format{symbol} = Common::Client::Current()->Locale()->currencyFormat()->symbol();
    }

    return \%format;
}

#
#
# ---------------------------------------------
# End package File::File
# ---------------------------------------------

# ---------------------------------------------
# Start package File::Files
# ---------------------------------------------
#
#
package RPS::File::Files;

use constant DB_TABLE => "file";

use lib '/app/tools/data_classes/lib';
use Items;
use base 'Items';

my @allowed_sorts =
  qw(f.orig_file_name f.service_id f.notes f.date_created f.units f.revenue f.records f.revenue_checked f.total_exceptions f.remaining_exceptions f.input_dist_fee_pct);

# --------------------------------
# Constructor
# --------------------------------
sub new {
    my $class = shift;
    my %args  = @_;

    my $self = $class->SUPER::new(@_);

    return $self;
}

sub StartPeriod {
    my $self      = shift;
    my $period_id = shift;
    return undef if ( !defined $period_id || $period_id !~ /^\d+$/ );

    # update finished files to be in specified period
    my $sql = "UPDATE " . DB_TABLE . " SET period_id = ? WHERE period_id = ? AND file_status = ?";

    return Common::RSApp::GetClientDB()->DoCmdWithPlaceholders($sql, [ $period_id, 0, File::File::STATUS_CLOSED ]);
}

sub GetNumClosed {
    my $class = shift;

    my $sql = "SELECT count(*) as closed_count FROM " . DB_TABLE . " WHERE period_id = ? AND file_status = ?";

    my $db = Common::RSApp::GetClientDB();
    my $sth = $db->DoCmdWithPlaceholders($sql, [0, File::File::STATUS_CLOSED]);
    if ($sth) {
        my $href = $sth->fetchrow_hashref();
        if ($href) {
            return $href->{closed_count};
        }
    }
    return undef;
}

# static method so also pass client-id
# returns -1 if both states of atomic exist for a given period
sub GetAtomicByPeriod {
    my $client_id = shift || return;
    my $period = shift;

    my $sql = "SELECT distinct(atomic) FROM " . DB_TABLE . " WHERE period_id = ?";
    my $db  = Common::RSApp::GetClientDB();
    my $sth = $db->DoCmdWithPlaceholders($sql, $period);

    return unless $sth;
    return -1 if ( $sth->rows > 1 );
    return $sth->fetchrow_arrayref()->[0];
}

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

    my $fName = $args{file_name};
    return undef if ( !defined $fName );

    my $sql = sprintf( "SELECT * FROM %s WHERE orig_file_name=%s", DB_TABLE, Common::RSApp::GetClientDB()->DBQuote($fName) );

    return $self->getByQuery($sql);
}

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

    my $md5_sum = $args{md5_sum};
    return undef if ( !defined $md5_sum );

    my $sql = "SELECT * FROM " . DB_TABLE . " WHERE file_md5sum=" . Common::RSApp::GetClientDB()->DBQuote($md5_sum);

    return $self->getByQuery($sql);
}

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

    my $distinct  = $args{distinct} || '';
    my $period_id = $args{period_id};
    return undef unless ( defined $period_id and $period_id =~ /^\d+$/ );

    my $sql = "SELECT $distinct f.* FROM " . DB_TABLE . " AS f LEFT JOIN service AS s ON f.service_id = s.service_id";

    # Sometimes we need to join with an additional table to figure things out.
    $sql .= " $args{table_join}" if ( $args{table_join} );

    # WHERE clause
    $sql .= " WHERE f.period_id=" . Common::RSApp::GetClientDB()->DBQuote($period_id);

    $sql .= " AND $args{filter_type}=" . Common::RSApp::GetClientDB()->DBQuote($args{filter_value}) if ( $args{filter_type} );

    #   Checking for NULL values
    $sql .= " AND $args{filter_type2} IS $args{filter_value2}" if ( $args{filter_type2} );

    # Sometimes we just want to pass a long string of criteria.
    $sql .= " AND $args{filter_string}" if ( $args{filter_string} );

    $sql .= " AND type_id = " . Common::RSApp::GetClientDB()->DBQuote($args{file_type_id}) if ( defined $args{file_type_id} );

    $sql .= " AND f.service_id = " . Common::RSApp::GetClientDB()->DBQuote($args{service_id}) if ( $args{service_id} );

    # need to apply currency conversion rate
    $sql .= " AND f.input_conversion_rate = " . Common::RSApp::GetClientDB()->DBQuote($args{filter_value3}) if ( defined $args{filter_type3} );

    # sales conversion_rate
    $sql .= " AND $args{filter_type4} $args{filter_value4} " if defined $args{filter_type4};

    # sales period date_end
    $sql .= " AND $args{filter_type5} = " . Common::RSApp::GetClientDB()->DBQuote($args{filter_value5}) if defined $args{filter_type5};

    my $sortby;
    if ( $args{sortby} ) {
        $sortby = "f." . $args{sortby};
    }

    # only allow some sorts, otherwise use default
    if ( !scalar( grep /$sortby/, @allowed_sorts ) ) {
        if ( $args{reverseSort} && $args{reverseSort} == 1 ) {
            $sortby = "s.service_name DESC, f.orig_file_name";
        } else {
            $sortby = "s.service_name, f.orig_file_name";
        }
    }

    $sql .= " ORDER BY $sortby";

    if ( $args{reverseSort} && $args{reverseSort} == 1 ) {
        $sql .= " DESC";
    }

    return $self->getByQuery($sql);
}

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

    my $period_id = $args{period_id};
    return undef unless ( defined $period_id and $period_id =~ /^\d+$/ );

    return undef unless ( $period_id =~ /^\d+$/ );

    my $sql = "SELECT f.* FROM " . DB_TABLE . " f LEFT JOIN service s ON f.service_id=s.service_id WHERE parent_file_id IS NULL";
    $sql .= " AND f.period_id BETWEEN 1 AND " . Common::RSApp::GetClientDB()->DBQuote($period_id)     if ( $period_id > 0 );
    $sql .= " AND $args{filter_type}=" . Common::RSApp::GetClientDB()->DBQuote($args{filter_value})   if ( $args{filter_type} );
    $sql .= " ORDER BY s.service_name, f.orig_file_name";

    return $self->getByQuery($sql);
}

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

    my $file_status = $args{file_status};
    return undef if ( !defined $file_status || $file_status !~ /^\d+$/ );

    my $sql = "SELECT * FROM " . DB_TABLE . " WHERE file_status=" . Common::RSApp::GetClientDB()->DBQuote($file_status);

    # only allow some sorts, otherwise use default
    my $sortby = $args{sortby};
    $sortby = "service_id" if ( !scalar( grep /$sortby/, @allowed_sorts ) );
    $sql .= " ORDER BY $sortby";

    return $self->getByQuery($sql);
}

sub GetImportQueue {
    my $self = shift;

    my $sql =
        "SELECT * FROM "
      . DB_TABLE
      . " WHERE period_id=0"
      . " AND (file_status="
      . File::File::STATUS_NEW()
      . " OR file_status="
      . File::File::STATUS_PROCESSING() . ")";

    return $self->getByQuery($sql);
}

sub GetNext {
    my $self = shift;

    return undef if ( !defined $self->{sth} );

    my $href = $self->{sth}->fetchrow_hashref();
    return undef if ( !defined $href || $href->{file_id} !~ /^\d+$/ );

    my $tmp = RPS::File::File->new( dbo => Common::RSApp::GetClientDB(), file_id => $href->{file_id} );
    return undef if ( !defined $tmp || $tmp->FileID !~ /^\d+$/ );

    return $tmp;
}

sub GetNumNeedCurrencyConversion {
    my $self = shift;

    my $sql = qq/
        SELECT count(*) as need_conversion_count
        FROM file
        WHERE period_id = ?
            AND file_status = ?
            AND input_conversion_rate = ?
            AND (revenue != ? OR revenue IS NOT NULL)
    /;

    my $db = Common::RSApp::GetClientDB();
    my $sth = $db->DoCmdWithPlaceholders($sql, [0, File::File::STATUS_OPEN, 1, 0]);
    if ($sth) {
        my $href = $sth->fetchrow_hashref();
        return $href->{need_conversion_count} if $href;
    }

    return;
}


1;