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
					date_created
					date_finished
					date_processed);

# 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
						DateCreated
						DateProcessed
						DateFinished
                        NeedsConversionRate
                        NonQualifiedErrorCount
                        QualifiedErrorCount
                        MapFaceStatus
						);

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;
	$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 = "SELECT sum(revenue) from user_input_revenue where file_id=" . $self->FileID . " group by file_id";
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    return undef unless $sth->rows;
    my $response = $sth->fetchrow();
    return $response;
}

sub GetMultipleTerritoryRevenue
{
    my $self = shift;

    my $sql = "SELECT associated_country,revenue from user_input_revenue where file_id=" . $self->FileID;
    $sql .= " order by associated_country";
    if($self->{SetRevenue} == 1) {
        $sql = "SELECT associated_country,revenue,currency_code from user_input_revenue u,sale s where u.file_id=" . $self->FileID;
        $sql .= " and u.file_id=s.file_id and u.associated_country=s.country_code group by s.country_code order by associated_country";
    }
    return undef if ($self->Physical == 1); 
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    my ($response,@return_data,$x);
    return undef unless $sth->rows;
    $x=0;
    while($response = $sth->fetchrow_hashref()) {
        push (@return_data,$response);
        $x++;
    }
    return @return_data;
}

sub GetMultipleTerritoryRevenueFromSale
{
    my $self = shift;

    my $sql = "SELECT distinct country_code,sum(price*units*conversion_rate) as revenue,currency_code from sale where file_id=" . $self->FileID;
    $sql .= " and free=0 group by country_code";
    return undef if ($self->Physical == 1);
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    my ($response,@return_data,$x);
    return undef unless $sth->rows;
    $x=0;
    while($response = $sth->fetchrow_hashref()) {
        push (@return_data,$response);
        $x++;
    }
    return @return_data;
}


sub GetInputConversionRate
{
    my $self = shift;
    my $currency = shift;
    my ($sql,$t_sql,$c_sql);
    if($currency eq "") {
        if ($self->Physical == 0) {
            $sql = "select sum(revenue*conversion_rate),sum(revenue) from user_input_conversion_rate where conversion_rate<>0 and file_id=".$self->FileID;
            my $csth = Common::RSApp::GetClientDB()->DoCmd($sql);
            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;
            }
        }
        elsif($self->Physical == 1) {
            $c_sql = "SELECT distinct conversion_rate from sale where file_id=".$self->FileID;
            my $csth = Common::RSApp::GetClientDB()->DoCmd($c_sql);
            return undef unless $csth->rows;
            my $rate = $csth->fetchrow();
            return $rate;
        }
    }
    else {
        $sql = "SELECT conversion_rate from user_input_conversion_rate where file_id=".$self->FileID." and associated_currency='".$currency."'";
        my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
        return undef unless $sth->rows;
        my $response = $sth->fetchrow();
        return $response;
    }
    return undef;
}

sub GetConversionRate
{
    my $self = shift;
    my ($sql,$sth);
    $sql = "SELECT sum(conversion_rate) from sale where file_id=".$self->FileID." and conversion_rate>0";
    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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 = "select revenue,round(sum(revenue*(dist_fee_pct*.01)),8) from user_input_dist_fee where file_id=".$self->FileID . " group by user_input_dist_fee_id";
        my $df_data = Common::RSApp::GetClientDB()->DoCmd($asql);
        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 = "SELECT dist_fee_pct from user_input_dist_fee where file_id=".$self->FileID;
        $sql .= " and associated_format='". $format . "'";
        my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
        return undef if($sth->rows==0);
        $response = $sth->fetchrow();
    }
    return $response;
}

sub GetDistFeePctFormats
{
    my $self = shift;
    my $sql;
    
    # 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();        
    
    
    if($self->InputDistFee) {
        $sql = "SELECT u.associated_format,u.dist_fee_pct from user_input_dist_fee u where u.file_id=".$self->FileID;
        $sql .= " order by u.associated_format";
    }
    else {
        $sql = "SELECT distinct sale.format_type from sale ";
        $sql .= "where sale.file_id=".$self->FileID . " order by sale.format_type";
    }
    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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{format}[$x] = $response[1];
        #$return_data{format}[$x] = $response[0] if($response[1] eq "");
        $return_data{value}[$x] = $response[1];
        $x++;
    }
    return %return_data;
}

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

    my $sql = "SELECT associated_currency,conversion_rate,revenue from user_input_conversion_rate where file_id=".$self->FileID;
    $sql .= " order by associated_currency";
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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(units*price) AS revenue
FROM sale
WHERE file_id=$file_id AND free=0
GROUP BY currency_code, conversion_rate
ORDER BY currency_code
EofSQL
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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 = "select sum((price*units)*conversion_rate) from sale where ";
    $sql .= "file_id=" . $self->FileID . " and currency_code='".$currency."'";
    
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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=".$self->FileID;
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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=".$self->FileID;
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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 HasChild
{
    my $self = shift;
    my $sql = "SELECT count(*) AS count FROM file WHERE parent_file_id=".$self->FileID;

	my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
	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 GetObjectXML
{
	my $self = shift;

	# 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') {
        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(sprintf("%.2f",$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);
            }
        }
    }

    $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};
#    $xml->{DateSalesFilePeriod} = Common::Client::Current()->Locale()->formatDate($self->SalesFileDateBegin) ." - " . Common::Client::Current()->Locale()->formatDate($self->SalesFileDateEnd);
#	($xml->{DateCreatedPretty} = $xml->{DateCreated}) =~ s/^\d\d(\d\d)-(\d\d)-(\d\d).+$/$2\/$3\/$1/;
#	($xml->{DateProcessedPretty} = $xml->{DateProcessed}) =~ s/^\d\d(\d\d)-(\d\d)-(\d\d).+$/$2\/$3\/$1/;
#	($xml->{DateFinishedPretty} = $xml->{DateFinished}) =~ s/^\d\d(\d\d)-(\d\d)-(\d\d).+$/$2\/$3\/$1/;

	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=".$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 except those with a CLOSED status
	#
	# Make sure the file isn't currently being processed.
	# Good idea, Justin.

	if($self->FileStatus != File::File::STATUS_CLOSED())
	{
        # JPK - We can no longer do this check, since the Import process
        # is not necessarily running on this host!
        # We could update this code to check the job queue, but that doesn't seem
        # that important at the moment...
        #
#		my $process = $self->ImportPID;
#		# -h don't print header
#		# -p lookup by pid
#		my @running = `ps -hp $process`;
#		if(!@running)
#		{
			# 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
				my $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=".$self->FileID;
				my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
				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=" . $file_id;
        $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    }
    if($self->InputDistFee && ($flavor eq "distfee" || $flavor eq "")) {
        $sql = "delete from user_input_dist_fee where file_id=" . $file_id;
        $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    }
    if($self->InputConversionRate && ($flavor eq "conversion" || $flavor eq "")) {
        $sql = "delete from user_input_conversion_rate where file_id=" . $file_id;
        $sth = Common::RSApp::GetClientDB(Common::RSApp::GetClientDB())->DoCmd($sql);
    }
    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=".$self->FileID 
            . " 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()->DoCmd($sql);
	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
	#

    my $fileID = $self->FileID;
    my $revenueFormula;
    my $unitsFormula;
    if ($self->Physical == 1) {
        $revenueFormula = 'total_revenue';
        $unitsFormula   = '(sales - returns)';
    } else {
        $revenueFormula = '(units * price)';
        $unitsFormula   = 'units';
    }

    my $sql = "SELECT count(*) FROM sale WHERE file_id=$fileID and conversion_rate > 0";
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);

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

    $sql = "SELECT DISTINCT(currency_code) FROM sale WHERE file_id=$fileID and free=0";
    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    if ($sth && $sth->rows > 1)
    {
        my $cCode = 'MUL';
        $self->CurrencyCode($cCode);
        $self->InputConversionRate(1);
    }
    elsif($sth->rows == 1) { 
        my $cCode = $sth->fetchrow_arrayref()->[0];
        $self->CurrencyCode($cCode);
        if($cCode ne $locale->currencyFormat()->currencyCode()) {
            $self->InputConversionRate(1);
        }
    }

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

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

	# 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=$fileID and free=0";
	}
	else
	{
		$whereClauseA = " WHERE file_id=$fileID";
	}

	$sql = "SELECT A.total_units, A.records, A.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"
		. " FROM sale"
		. $whereClauseA
		. " ) A,"

		# B - query
		. " ( "
		. " SELECT count(*) AS total_exceptions"
		. " FROM sale"
		. " WHERE file_id=$fileID"
		. " 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=$fileID"
		. " 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);
		# 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=$fileID" .
              " AND import_status = ".File::Sale::STATUS_DONT_MATCH;
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    my @tmp = $sth->fetchrow_array();
    $retval->{lines} = $tmp[0] || 0;
    
    my ($units, $revenue);
    if ($self->Physical == 1)
    {
        $units = 'sales-returns';
        $revenue = 'total_revenue';
    }
    else
    {
        $units = 'units';
        $revenue = 'units * price';
    }
    $sql = "SELECT SUM($units) FROM sale WHERE file_id=$fileID" .
           " AND import_status = ". File::Sale::STATUS_DONT_MATCH;

    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    @tmp = $sth->fetchrow_array();
    $retval->{units} = $tmp[0] || 0;

    $sql = "SELECT SUM($revenue * conversion_rate) FROM sale".
           " WHERE file_id=$fileID" .
           " AND import_status = ".File::Sale::STATUS_DONT_MATCH;

    $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    @tmp = $sth->fetchrow_array();
    $retval->{revenue} = $tmp[0] || 0;
    #$retval->{revenue} = ($sth->fetchrow_array())[0] || 0;

    return $retval;
}

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

    my $retval;

    my $units = $self->Physical == 1 ? 'sales-returns' : 'units';
    my $sql = "SELECT SUM($units) FROM sale WHERE file_id = ".$self->FileID.
              " AND free = 1";
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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=".$self->PeriodID;
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
    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);
    # 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 = $fileID AND associated_currency = \"$currencyCode\"";
    my $sth = Common::RSApp::GetClientDB()->DoCmd($querySql);
    my $exists = $sth->fetchrow();
    if ($exists > 0) 
    {
    	 my $updateSql = "UPDATE user_input_conversion_rate SET revenue = $revenue WHERE file_id = $fileID AND associated_currency = \"$currencyCode\"";
    	 $sth = Common::RSApp::GetClientDB()->DoCmd($updateSql);
    }    
    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()->DoCmd("select distinct " . $pieces{$flavor}{initial_field} . " from sale where file_id=". $file_id);
        $associated_value = $sth->fetchrow();
    }
    if($flavor ne "revenue") {
        my $sql;
        $sql = "SELECT sum(units*price) from sale where file_id=".$file_id." and ".$pieces{$flavor}{initial_field}."='".$associated_value."'";
        $sql = "SELECT sum(total_revenue) from sale where file_id=".$file_id." and ".$pieces{$flavor}{initial_field}."='".$associated_value."'" if($self->Physical == 1);
        $sth = Common::RSApp::GetClientDB()->DoCmd($sql);
        $raw_revenue = $sth->fetchrow();
    }
    $querySql = "select count(*) from ".$pieces{$flavor}{table} . " where file_id=".$file_id." and ".$pieces{$flavor}{associated_field}."='";
    $querySql .= $associated_value."'";
    $sth = Common::RSApp::GetClientDB()->DoCmd($querySql);
    @tmp = $sth->fetchrow_array();
    $insertSql = "insert into ".$pieces{$flavor}{table}." (file_id,".$pieces{$flavor}{associated_field}.",".$pieces{$flavor}{key_field};
    $insertSql .= ",revenue" if($flavor ne "revenue");
    $insertSql .= ") values (";
    $insertSql .= $file_id . ",'" . $associated_value . "','" . $key_value . "'";
    $insertSql .= ",".$raw_revenue if($flavor ne "revenue");
    $insertSql .= ")";
    $updateSql = "update " . $pieces{$flavor}{table} . " set ".  $pieces{$flavor}{key_field} . "='" . $key_value . "'";
    $updateSql .= ",revenue=".$raw_revenue." " if($flavor ne "revenue"); 
    $updateSql .= "where file_id=" . $file_id . " and " . $pieces{$flavor}{associated_field} . "='" . $associated_value . "'";
    $thisSql = $updateSql if($tmp[0]>0);
    $thisSql = $insertSql if($tmp[0]==0);
    $sth = Common::RSApp::GetClientDB()->DoCmd($thisSql);
    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='. $self->FileID;
    my $sth = Common::RSApp::GetClientDB()->DoCmd($sql);

    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->InputRevenue)
		{
			#$new_file->InputRevenue(1);
            #$self->CreateUpdateUserInput($self->FileID,'revenue',$new_file->Revenue,'');
		}
        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,);

	    #if ($self->InputRevenue)
		#{
		#	$self->InputRevenue($self->Revenue);
		#}

        $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 ) {
            $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 ) {
            $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=$period_id"
        . " WHERE period_id=0"
        . " AND file_status=".File::File::STATUS_CLOSED;

    return Common::RSApp::GetClientDB()->DoCmd($sql);
}

sub GetNumClosed
{
	my $class = shift;

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

	my $db = Common::RSApp::GetClientDB();

	my $sth = $db->DoCmd($sql);
	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=$period";
    my $db = Common::RSApp::GetClientDB();
    my $sth = $db->DoCmd($sql);

    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 = shift;
	my %args = @_;

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

	my $sql = "SELECT f.* FROM (".DB_TABLE." f LEFT JOIN service 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});
			
		$sql .= " WHERE f.period_id=".Common::RSApp::GetClientDB()->DBQuote($period_id);
		
    $sql .= " AND $args{filter_type}='$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 = $args{file_type_id}" if (defined $args{file_type_id});
	
	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 $period_id" if ($period_id > 0);
    $sql .= " AND $args{filter_type}='$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;
}

###
1;# Play nicely.
###