package RPS::File::Sale;

use strict;
use Carp;
use Date::Calc qw(Days_in_Month);
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;
use Common::Consts;
use Common::Assert;

use lib '/app/tools/rps/lib';
use RPS::File::File;
use RPS::DB::Item::SaleImportError;

use lib '/app/tools/data_classes/lib';
use File::Sale;
use base 'File::Sale';

# !!! These status values will live in the base class.
# !!! They are generic to the _process_.
#
# public constants
#use constant STATUS_MATCH			=>	1;
#use constant STATUS_NOMATCH			=>	2;
#use constant STATUS_MULTIMATCH		=>	3;
#use constant STATUS_MAPPED			=>	4;
#use constant STATUS_UNRECOVERABLE	=>	5;
#use constant STATUS_AUTO_MAPPED     =>  6;
#use constant STATUS_BATCH_MAPPED    =>  7;
#use constant STATUS_DONT_MATCH      =>  8;

use constant TYPE_ALBUM => 'A';
use constant TYPE_TRACK => 'T';
## leaving TYPE_PHYSICAL for now for back-compat until we've figured it out better
use constant TYPE_PHYSICAL       => '2';
use constant TYPE_LP             => '1';
use constant TYPE_LP5            => 'P';
use constant TYPE_CD             => '2';
use constant TYPE_VHS            => '3';
use constant TYPE_CASS           => '4';
use constant TYPE_VCD            => '5';
use constant TYPE_EP             => '7';
use constant TYPE_MD             => '8';
use constant TYPE_DVD            => '9';
use constant TYPE_BLURAY         => 'U';
use constant TYPE_MASTER         => 'M';
use constant TYPE_CAS_SIN        => 'K';
use constant TYPE_CD_SIN         => 'C';
use constant TYPE_DVD_CD_SET     => 'D';
use constant TYPE_DBL_CD         => 'B';
use constant TYPE_LICENSE_INCOME => 'L';

use constant FORMAT_DOWNLOAD        => 'D';
use constant FORMAT_DOWNLOADPREMIUM => 'H';
use constant FORMAT_DOWNLOADUPGRADE => 'I';
use constant FORMAT_STREAM          => 'S';
use constant FORMAT_TETHERED        => 'T';
use constant FORMAT_RINGTONE        => 'R';

#use constant FORMAT_MECHANICAL      =>  'M'; # Retired (FB1324)
use constant FORMAT_JUKEBOX => 'J';
use constant FORMAT_VIDEO   => 'V';    # Retired (FB1324)

#use constant FORMAT_VIDEOSTREAM     =>  'W'; # Retired (FB1324)
use constant FORMAT_DUALDOWNLOAD    => 'E';
use constant FORMAT_BACKGROUNDMUSIC => 'B';

#use constant FORMAT_RADIO           =>  'F'; # Retired (FB1324)
#use constant FORMAT_TELEVISION      =>  'U'; # Retired (FB1324)
use constant FORMAT_VPD => 'P';    ## variable price download

use constant FORMAT_PERFORMANCE => 'O';    ## performance income

use constant FORMAT_SYNC => 'X';    ## Orchard Synchronization Revenue

# Sound Exchange Performance Income formats.  If a category of service is
# specified, you should use the format (Ephemeral or Non-Ephemeeral) with
# the category of service appended to the constant name.  Otherwise, use
# FORMAT_EPHEMERAL or FORMAT_NON_EPHEMERAL if no category was specified.
#
# Tech Note: If you're adding another PI format type, don't forget to update
# the GetFormatType() and IsPerformanceIncome() methods (both are in this
# file).
#
#   _WEBCAST  = Webcasting
#   _SDARS    = Satellite Radio
#   _BES      = Business Establishment Service
#   _CABLERAD = Cable Radio
#   _PES      = Pre-Existing Service
#   _ATU      = Admin True Up
#   _IP       = International Proxies
use constant FORMAT_EPHEMERAL            => 'L';
use constant FORMAT_EPHEMERAL_WEBCAST    => '!';
use constant FORMAT_EPHEMERAL_SDARS      => '@';
use constant FORMAT_EPHEMERAL_BES        => '#';
use constant FORMAT_EPHEMERAL_CABLERAD   => '$';
use constant FORMAT_EPHEMERAL_PES        => '%';
use constant FORMAT_EPHEMERAL_ATU        => '[';
use constant FORMAT_EPHEMERAL_IP         => ']';
use constant FORMAT_EPHEMERAL_SETTLEMENT => ':';

use constant FORMAT_NON_EPHEMERAL            => 'N';
use constant FORMAT_NON_EPHEMERAL_WEBCAST    => '&';
use constant FORMAT_NON_EPHEMERAL_SDARS      => '*';
use constant FORMAT_NON_EPHEMERAL_BES        => '(';
use constant FORMAT_NON_EPHEMERAL_CABLERAD   => ')';
use constant FORMAT_NON_EPHEMERAL_PES        => '_';
use constant FORMAT_NON_EPHEMERAL_ATU        => '{';
use constant FORMAT_NON_EPHEMERAL_IP         => '}';
use constant FORMAT_NON_EPHEMERAL_SETTLEMENT => ';';

# wireless format types
use constant FORMAT_RINGBACK => '1';

#use constant FORMAT_MASTERTONE      =>  '2'; # Retired (FB1324)
#use constant FORMAT_ANIMATEDRINGTONE=>  '3'; # Retired (FB1324)
#use constant FORMAT_VOICERINGER     =>  '4'; # Retired (FB1324)
#use constant FORMAT_VIDEORINGER     =>  '5'; # Retired (FB1324)
#use constant FORMAT_MIDI            =>  '6'; # Retired (FB1324)
#use constant FORMAT_GRAPHIC         =>  '7'; # Retired (FB1324)
#use constant FORMAT_SMSTONE         =>  '8'; # Retired (FB1324)

## although not technically correct, using LP for all 12"/10" vinyl
## and EP for all 7" vinyl

use constant PLEVEL_UNKNOWN => 0;
use constant PLEVEL_FULL    => 1;
use constant PLEVEL_MID     => 2;
use constant PLEVEL_BUDGET  => 3;

#use constant PLEVEL_ALBUM_DL        =>  4;
#use constant PLEVEL_TRACK_DL        =>  5;
use constant PLEVEL_PROMO => 6;

use constant CHANNEL_RETAIL    => 1;
use constant CHANNEL_MILITARY  => 2;
use constant CHANNEL_CLUB      => 3;
use constant CHANNEL_MAILORDER => 4;
use constant CHANNEL_DIRECT    => 5;

# private constants
# !!! Deprecating the constant in favor of an overridable method.
#
#use constant DB_TABLE => "sale";
sub _dbTable { return 'sale'; }

# private attributes
my @attributes = qw(sale_id
  file_id
  service_id
  import_status
  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
  units
  price
  wholesale_price
  retail_price
  currency_code
  conversion_rate
  country_code
  line_num
  map_id
  wholesale_rate
  retail
  payout_type
  price_type
  sales
  sales_revenue
  returns
  returns_revenue
  total_revenue
  average_price
  price_level
  channel
  outlet
  configuration
  date_created
  mcps_adjustment
  comments
  mechanical_royalty_status
  ca_mechanical_royalty_status
  uk_mechanical_royalty_status
  artist_royalty_status
  label_royalty_status
  free
  gross_revenue);

# what should show up in xml by default
my @xml_attributes = qw(SaleID
  FileID
  ServiceID
  ImportStatus
  ProductID
  ProductType
  FormatType
  MediaType
  DateBegin
  DateEnd
  ServiceProductID
  ClientProductID
  UPC
  ISRC
  ArtistName
  AlbumName
  TrackName
  LabelName
  TrackNum
  Units
  Price
  WholesalePrice
  RetailPrice
  LineNum
  CurrencyCode
  ConversionRate
  CountryCode
  MapID
  WholesaleRate
  Retail
  PayoutType
  PriceType
  Sales
  SalesRevenue
  Returns
  ReturnsRevenue
  TotalRevenue
  AveragePrice
  PriceLevel
  Channel
  Outlet
  Configuration
  Free
  GrossRevenue
);

####
#### MERGE - moved this below the constant declarations so that
#### references to them in Sale::Match will compile. -jff-
####
use lib '/app/tools/rps/lib';
use RPS::Sale::Match;

use lib '/app/tools/data_classes/lib';
use Item;

#use base 'Item';
use 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{sale_id} ) {
        $self->Load( sale_id => $args{sale_id} );
        $self->{dirty} = 0;
    }
}

# Here's the new 'virtual constructor' that will be invoked by the Importer class.
#
sub CreateFromSaleRec {
    my ( $class, %args ) = @_;

    my $saleRec        = $args{saleRec};
    my $file           = $args{file};
    my $hasError       = $args{hasError};
    my $linesImported  = $args{linesImported};
    my $salesBatchMode = $args{salesBatchMode};
    my $salesBatchSize = $args{salesBatchSize};

    assert($saleRec);
    assert($file);

    my $dbo = $salesBatchMode ? Common::RSApp::GetClientDBBatchMode() : Common::RSApp::GetClientDB();
    my $self = $class->new( dbo => $dbo );

    # The 'sale rec' structure doesn't have a file_id member.
    $self->FileID( $file->FileID );

    $self->_fillInFieldsFromSaleRec($saleRec);

    # If this sale had an import error, don't try to find a match for it.
    # We'll do that later after the error is resolved.
    unless ($hasError) {
        $self->_findMatch( file => $file );
    }

    if ( $salesBatchMode ) {
        $self->BatchSave($salesBatchSize, $linesImported);
    } else {
        $self->Save();
    }

    return $self;
}

sub _fillInFieldsFromSaleRec {
    my ( $self, $saleRec ) = @_;
    assert($saleRec);

    $self->AlbumName( $saleRec->albumName );
    $self->ArtistName( $saleRec->artistName );
    $self->TrackName( $saleRec->trackName );
    $self->LabelName( $saleRec->labelName );
    $self->AveragePrice( $saleRec->averagePrice );
    $self->Channel( $saleRec->channel );
    $self->ClientProductID( $saleRec->clientProductID );
    $self->Configuration( $saleRec->configuration );
    $self->ConversionRate( $saleRec->conversionRate );
    $self->CountryCode( $saleRec->countryCode );
    $self->CurrencyCode( $saleRec->currencyCode );
    $self->DateBegin( $saleRec->dateBegin );
    $self->DateEnd( $saleRec->dateEnd );
    $self->FormatType( $saleRec->formatType );
    $self->Free( $saleRec->free );
    $self->ISRC( $saleRec->isrc );
    $self->LineNum( $saleRec->lineNum );
    $self->MediaType( $saleRec->mediaType );
    $self->Outlet( $saleRec->outlet );
    $self->PayoutType( $saleRec->payoutType );
    $self->PriceLevel( $saleRec->priceLevel );
    $self->WholesalePrice( $saleRec->wholesalePrice );
    $self->RetailPrice( $saleRec->retailPrice );
    $self->Price( $saleRec->price );
    $self->PriceType( $saleRec->priceType );
    $self->ProductType( $saleRec->productType );
    $self->Retail( $saleRec->retail );
    $self->ReturnsRevenue( $saleRec->returnsRevenue );
    $self->Returns( $saleRec->returns );
    $self->SalesRevenue( $saleRec->salesRevenue );
    $self->Sales( $saleRec->sales );
    $self->ServiceID( $saleRec->serviceID ) if ( $saleRec->serviceID );
    $self->ServiceProductID( $saleRec->serviceProductID );
    $self->TotalRevenue( $saleRec->totalRevenue );
    $self->TrackNum( $saleRec->trackNum );
    $self->Units( $saleRec->units );
    $self->UPC( $saleRec->upc );
    $self->WholesaleRate( $saleRec->wholesaleRate );
    $self->McpsAdjustment( $saleRec->mcps_adjustment );
    $self->Comments( $saleRec->comments );
    $self->UKMechanicalRoyaltyStatus( $saleRec->uk_mechanical_royalty_status );
    $self->CAMechanicalRoyaltyStatus( $saleRec->ca_mechanical_royalty_status );
    $self->MechanicalRoyaltyStatus( $saleRec->mechanical_royalty_status );
    $self->ArtistRoyaltyStatus( $saleRec->artist_royalty_status );
    $self->LabelRoyaltyStatus( $saleRec->label_royalty_status );
    $self->GrossRevenue( $saleRec->grossRevenue );
}

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

    # JPK - There needs to be only one Matcher object per client.  So we'll let the App object manage it.
    #
    my $globalCache = Common::RSApp::GetRAMCache();
    if ( !defined $globalCache->{_matcher} ) {
        $globalCache->{_matcher} = RPS::Sale::Match->new( client_id => Common::RSApp::GetClientID() );
    }

    return $globalCache->{_matcher};
}

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

    my $file = $args{file};
    assert($file);

    my $data = {
        service_id         => $file->ServiceID(),
        media_type         => $self->MediaType,
        album              => substr( $self->AlbumName, 0, 255 ),          # Truncating this to match the DB column.
        artist             => substr( $self->ArtistName, 0, 255 ),         # Truncating this to match the DB column.
        client_product_id  => $self->ClientProductID,
        format             => $self->FormatType,
        isrc               => $self->ISRC,
        product_type       => $self->ProductType,
        service_product_id => substr( $self->ServiceProductID, 0, 30 ),    # Truncating this to match the DB column.
        track_num          => $self->TrackNum,
        track              => substr( $self->TrackName, 0, 255 ),          # Truncating this to match the DB column.
        upc                => $self->UPC,
        outlet             => $self->Outlet,
    };

    #    my $match = RPS::Sale::Match->new(client_id => Common::RSApp::GetClientID());
    my $match = $self->_getMatcher();

    my $result = $match->FindBestMatch( data => $data, min_match_level => 90, skip_rec => 1 );

    $self->ImportStatus( $result->ImportStatus );
    if ( $self->ImportStatus == File::Sale::STATUS_MATCH ) {
        $self->ProductID( $result->ProductIDs->[0] );
    } elsif ( $self->ImportStatus == File::Sale::STATUS_MAPPED ) {
        $self->ProductID( $result->ProductIDs->[0] );
        $self->MapID( $result->MapID );
    } elsif ( $self->ImportStatus == File::Sale::STATUS_AUTO_MAPPED ) {
        $self->ProductID( $result->ProductIDs->[0] );
        $self->MapID( $result->MapID );
    } elsif ( $self->ImportStatus == File::Sale::STATUS_BATCH_MAPPED ) {
        $self->ProductID( $result->ProductIDs->[0] );
        $self->MapID( $result->MapID );
    } elsif ( $self->ImportStatus == File::Sale::STATUS_DONT_MATCH ) {
        $self->ProductID( $result->ProductIDs->[0] );
        $self->MapID( $result->MapID );
    }
}

# --------------------------------
# Properties
# --------------------------------

sub SaleID {
    my $self = shift;
    $self->{sale_id};
}

sub FileID {
    my $self = shift;

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

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 ImportStatus {
    my $self = shift;

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

sub ProductID {
    my $self = shift;

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

sub ProductType {
    my $self = shift;

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

sub FormatType {
    my $self = shift;

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

sub MediaType {
    my $self = shift;

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

sub DateBegin {
    my $self = shift;

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

sub DateEnd {
    my $self = shift;

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

sub ServiceProductID {
    my $self = shift;

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

sub ClientProductID {
    my $self = shift;

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

sub UPC {
    my $self = shift;

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

sub ISRC {
    my $self = shift;

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

sub ArtistName {
    my $self = shift;

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

sub AlbumName {
    my $self = shift;

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

sub TrackName {
    my $self = shift;

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

sub LabelName {
    my $self = shift;

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

sub TrackNum {
    my ($self, $rvalue) = @_;

    my $max = 255;

    # setting the value
    if ( defined $rvalue && $rvalue ne $self->{track_num} ) {
        $self->{track_num} = $rvalue > $max ? $max : $rvalue;
        $self->{dirty}     = 1;
    }

    return $self->{track_num};
}

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 WholesalePrice {
    my $self = shift;

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

sub RetailPrice {
    my $self = shift;

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

sub Price {
    my $self = shift;

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

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 ConversionRate {
    my $self = shift;

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

sub CountryCode {
    my $self = shift;

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

sub LineNum {
    my $self = shift;

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

sub MapID {
    my $self = shift;

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

sub WholesaleRate {
    my $self = shift;

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

sub Retail {
    my $self = shift;

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

sub PayoutType {
    my $self = shift;

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

sub PriceType {
    my $self = shift;

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

sub Sales {
    my $self = shift;

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

sub SalesRevenue {
    my $self = shift;

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

sub Returns {
    my $self = shift;

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

sub ReturnsRevenue {
    my $self = shift;

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

sub TotalRevenue {
    my $self = shift;

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

sub AveragePrice {
    my $self = shift;

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

sub Free {
    my $self = shift;

    # setting the value
    if (@_) {
        my ($rvalue) = @_;
        my $flag = ( $rvalue ? 1 : 0 );
        if ( $flag != $self->{free} ) {
            $self->{free}  = $flag;
            $self->{dirty} = 1;
        }
    }
    return $self->{free};
}

sub PriceLevel {
    my $self = shift;

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

sub Channel {
    my $self = shift;

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

sub Outlet {
    my $self = shift;

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

sub Configuration {
    my $self = shift;

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

sub McpsAdjustment {
    my $self = shift;

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

sub Comments {
    my $self = shift;

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

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 UKMechanicalRoyaltyStatus {
    my $self = shift;

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

sub CAMechanicalRoyaltyStatus {
    my $self = shift;

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

sub MechanicalRoyaltyStatus {
    my $self = shift;

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

sub ArtistRoyaltyStatus {
    my $self = shift;

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

sub LabelRoyaltyStatus {
    my $self = shift;

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

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;

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

    $xml->{PricePretty} = $xml->{Price};
    $xml->{PricePretty} =~ s/^(\d+\.\d\d).+$/$1/;

    # The idea here is that we want a price with between 2 and 8 decimal places
    # AND no trailing zeroes unless those are the only two numbers after the decimal.
    $xml->{PriceSmart} = $xml->{Price};
    $xml->{PriceSmart} =~ s/\.0*$|0*$//;
    $xml->{PriceSmart} =~ s/^(\d+)$/$1\.00/;

    $xml->{RevenuePretty} = $xml->{TotalRevenue};
    $xml->{RevenuePretty} =~ s/^(\d+\.\d\d).+$/$1/;

    $xml->{GrossRevenuePretty} = $xml->{GrossRevenue};
    $xml->{GrossRevenuePretty} =~ s/^(\d+\.\d\d).+$/$1/;

    $xml->{FormatTypePretty} = GetFormatType( $xml->{FormatType} );

    return $xml;
}

sub IsAlbum {
    my $self       = shift;
    my $albumTypes = join( '',
        TYPE_ALBUM, TYPE_CD_SIN, TYPE_DVD_CD_SET, TYPE_DBL_CD, TYPE_LP, TYPE_LP5, TYPE_CD,
        TYPE_VHS,   TYPE_CASS,   TYPE_VCD,        TYPE_EP,     TYPE_MD, TYPE_DVD, TYPE_BLURAY,
        TYPE_CAS_SIN,
    );

    return uc $self->ProductType() =~ m/^[$albumTypes]$/ ? 1 : 0;
}

sub IsTrack {
    my $self = shift;

    return uc $self->ProductType() eq TYPE_TRACK ? 1 : 0;
}

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

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

    my $sth = $self->{dbo}->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 sale record for sale_id=$args{sale_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|sale_id)$/;

        my $new_val = $self->{$attrib};
        next if ( !defined $new_val );

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

    # update or insert?
    if ( defined $self->{sale_id} && $self->{sale_id} =~ /^\d+$/ ) {
        my $sql =
          "UPDATE "
          . $self->_dbTable( $self->_dbTable() )
          . " SET " . join( ', ', @set_fields )
          . " WHERE sale_id = " . $self->{dbo}->DBQuote( $self->{sale_id} );
        my $sth = $self->{dbo}->DoCmd($sql);

        if ( !defined $sth ) {
            $self->{errstr} = "update command failed: $DBI::errstr";
            return undef;
        }
    } else {
        my $sql = "INSERT INTO " . $self->_dbTable() . " SET date_created=NOW(), " . join( ', ', @set_fields );
        my $sth = $self->{dbo}->DoCmd($sql);
        if ( !defined $sth ) {
            $self->{errstr} = "insert command failed: $DBI::errstr";
            return undef;
        } else {
            $self->{sale_id} = $self->{dbo}->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(sale_id => $self->{sale_id});

    $self->{dirty} = 0;

    return 1;
}

sub BatchSave {
    my ($self, $batchSize, $linesImported) = @_;

    return if !$self->{dirty};

    my (@fields, @values);
    foreach my $name (@attributes) {
        next if $name =~ /^date_created|date_modified|sale_id$/;
        next if !defined $self->{$name};
        push @fields, $name;
        push @values, $self->{$name};
    }

    my $sql = "INSERT INTO " . $self->_dbTable() . " (date_created, %s) VALUES (NOW(), %s)";
    $sql = sprintf( $sql, join(', ', @fields), join(', ', map {'?'} @fields) );

    my $dbh = $self->{dbo}->DBH();
    my $sth = $dbh->prepare($sql);
    $sth->execute(@values);
    $self->{sale_id} = $dbh->last_insert_id(undef, undef, $self->_dbTable(), 'ID_COLUMN');

    $dbh->commit unless $linesImported % $batchSize;

    $self->{dirty} = 0;

    return 1;
}


sub MassUpdate {
    my ( $self, $hData, $aAttrs ) = @_;
    return unless ref $hData eq 'HASH';
    return unless ref $aAttrs eq 'ARRAY';

    while ( my ( $values, $aSaleIDs ) = each %$hData ) {
        my @values = map { $self->{dbo}->DBQuote($_) } split '~~', $values;
        return unless @values == @$aAttrs;

        my @pairs;
        for ( my $i = 0 ; $i < @$aAttrs ; $i++ ) {

            # skip records with default value RSD-3400
            next if $values[$i] =~ /^'0?'$/;

            push @pairs, $aAttrs->[$i] . "=" . $values[$i];
        }

        my $sql =
            "UPDATE "
          . $self->_dbTable( $self->_dbTable() ) . " SET "
          . join( ', ', @pairs )
          . " WHERE sale_id IN("
          . join( ',', @$aSaleIDs ) . ")";
        my $sth = $self->{dbo}->DoCmd($sql);
        unless ( defined $sth ) {
            $self->{errstr} = "update command failed: $DBI::errstr";
            return;
        }
    }

    return 1;
}

sub Unmatch {
    my $self = shift;

    # you can't unmatch if it really is a match.
    # it just wouldn't be right.
    return undef if ( $self->ImportStatus == File::Sale::STATUS_MATCH() );

    my $map_id = $self->MapID;

    # set to the string "null" so that the db picks up the change
    $self->ProductID("null");
    $self->MapID("null");
    $self->ImportStatus(File::Sale::STATUS_NOMATCH)
      if ( $self->ImportStatus == File::Sale::STATUS_MAPPED()
        || $self->ImportStatus == File::Sale::STATUS_AUTO_MAPPED()
        || $self->ImportStatus == File::Sale::STATUS_BATCH_MAPPED() );
    $self->Save();

    # we really want the value to be null so set it for reals here
    $self->ProductID(undef);
    $self->MapID(undef);

    return 1;

}

sub MakeMatch {
    my $self = shift;
    my $product_id = shift || return undef;

    # Let's make sure it's not already matched.
    my $already_matched = $self->ProductID;
    return undef if ($already_matched);

    my $file = RPS::File::File->new( file_id => $self->FileID );

    my $match_data = {
        product_type       => $self->ProductType,
        format             => $self->FormatType,
        media_type         => $self->MediaType,
        upc                => $self->UPC,
        isrc               => $self->ISRC,
        artist             => $self->ArtistName,
        album              => $self->AlbumName,
        track              => $self->TrackName,
        track_num          => $self->TrackNum,
        service_id         => $file->ServiceID,
        service_product_id => $self->ServiceProductID,
    };

    # returns result object
    my $matcher = RPS::Sale::Match->new( client_id => $self->{client_id} );
    my $map_id = $matcher->MakeMatch( data => $match_data, product_id => $product_id );

    if ($map_id) {
        $self->ProductID($product_id);
        $self->MapID($map_id);
        $self->Save();
        return 1;
    } else {
        print STDERR $matcher->errstr . "\n";
        return undef;
    }
}

sub CanUnmatch {
    my $self = shift;

    my $map_id = $self->MapID;
    return undef unless ($map_id);

    return undef if ( $self->ImportStatus == File::Sale::STATUS_MATCH() );

    my $sql = qq/
        SELECT count(*) as count
        FROM sale s, file f
        WHERE 1 = 1
            AND s.map_id = ?
            AND s.file_id = f.file_id
            AND f.file_status = ?
    /;
    my $sth = $self->{dbo}->DoCmdWithPlaceholders($sql, [ $map_id, File::File::STATUS_CLOSED() ]);

    return undef unless ($sth);

    my $href = $sth->fetchrow_hashref();
    return undef if ( !( defined $href ) || $href->{count} > 0 );
    return 1;
}

sub GetSalesToUnmatch {
    my $self = shift;

    ## returns a hash of sale_id => file_id for all sales
    ## using the same map as this one.

    my $map_id = $self->MapID;
    return undef unless ($map_id);

    my $sql = "SELECT sale_id, file_id FROM sale WHERE map_id = ?";
    my $sth = $self->{dbo}->DoCmdWithPlaceholders( $sql, [$map_id] );

    return undef unless ($sth);

    my %records;

    while ( my ( $sale_id, $file_id ) = $sth->fetchrow_array ) {
        $records{$sale_id} = $file_id;
    }

    return \%records;
}

sub GetPrevNextRankedIDs {
    my $self     = shift;
    my $ret_href = {};

    my $sql = "SET \@i:=0";
    $self->{dbo}->DoCmd($sql);

    $sql =
        "SELECT  \@i:=\@i+1 as row_num, sale_id, product_id FROM "
      . $self->_dbTable()
      . " WHERE file_id= ?"
      . " AND import_status IN (?,?,?,?)"
      . " ORDER BY units DESC";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders(
        $sql,
        [
            $self->FileID,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );

    if ( $sth && $sth->rows > 0 ) {

        # [0] = row_num
        # [1] = sale_id
        # [2] = product_id
        my $all_aref = $sth->fetchall_arrayref();

        my $unfixed = [];
        foreach my $aref (@$all_aref) {
            if ( !$aref->[2] || $aref->[1] == $self->SaleID ) {
                push @$unfixed, $aref;
            }
        }

        my $i       = 0;
        my $last_id = $#$all_aref;
        while ( $i <= $last_id ) {
            my $aref = $all_aref->[$i];

            if ( $self->SaleID == $aref->[1] ) {
                $ret_href->{prev_id} = ( $i > 0 )        ? $all_aref->[ $i - 1 ]->[1] : undef;
                $ret_href->{next_id} = ( $i < $last_id ) ? $all_aref->[ $i + 1 ]->[1] : undef;
                last;
            }
            $i++;
        }

        $i       = 0;
        $last_id = $#$unfixed;
        while ( $i <= $last_id ) {
            my $aref = $unfixed->[$i];

            if ( $self->SaleID == $aref->[1] ) {
                $ret_href->{prev_unfixed_id} = ( $i > 0 )        ? $unfixed->[ $i - 1 ]->[1] : undef;
                $ret_href->{next_unfixed_id} = ( $i < $last_id ) ? $unfixed->[ $i + 1 ]->[1] : undef;
                last;
            }
            $i++;
        }
    }

    return $ret_href;
}

sub GetPreviousErrorID {
    my $self = shift;

    my $sql = "SELECT max(sale_id) as sale_id FROM " . $self->_dbTable();
    $sql .= " WHERE file_id = ?";
    $sql .= " AND sale_id < ?";
    $sql .= " AND import_status IN (?,?,?,?)";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders(
        $sql,
        [
            $self->FileID,
            $self->SaleID,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetPreviousUnfixedID {
    my $self = shift;

    my $sql = "SELECT max(sale_id) as sale_id FROM " . $self->_dbTable() . " WHERE sale_id < ?";
    $sql .= " AND file_id = ?";
    $sql .= " AND import_status IN (?,?,?,?)";
    $sql .= " AND map_id IS NULL";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders(
        $sql,
        [
            $self->SaleID,
            $self->FileID,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetNextErrorID {
    my $self = shift;

    my $sql = "SELECT min(sale_id) as sale_id FROM " . $self->_dbTable() . " WHERE sale_id > ?";
    $sql .= " AND file_id = ?";
    $sql .= " AND import_status IN (?,?,?,?)";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders(
        $sql,
        [
            $self->SaleID,
            $self->FileID,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetNextUnfixedID {
    my $self = shift;

    my $sql = "SELECT min(sale_id) as sale_id FROM " . $self->_dbTable() . " WHERE sale_id > ?";
    $sql .= " AND file_id = ?";
    $sql .= " AND import_status IN (?,?,?,?)";
    $sql .= " AND map_id IS NULL";
    my $sth = $self->{dbo}->DoCmdWithPlaceholders(
        $sql,
        [
            $self->SaleID,
            $self->FileID,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetSearchMatches {
    my $self        = shift;
    my %args        = @_;
    my $search_for  = $args{search} || return undef;
    my $search_type = $args{search_type} || "smart";
    my $limit       = $args{num} || 100;

    my $search_data = {
        upc        => $self->UPC,
        isrc       => $self->ISRC,
        artist     => $self->ArtistName,
        album      => $self->AlbumName,
        track      => $self->TrackName,
        format     => $self->FormatType,
        media_type => $self->MediaType,
    };

    # returns result object
    my $matcher = RPS::Sale::Match->new( client_id => $self->{client_id} );

    #print STDERR "searching $search_for ($search_type)\n";
    my $result = $matcher->SearchCatalog(
        keyword      => $search_for,
        product_type => $self->ProductType,
        data         => $search_data,
        type         => $search_type
    );

    my $matches = scalar( @{ $result->SearchProductIDs } ) if ($result);

    # search results
    if ($matches) {
        return $result->SearchProductIDs;
    }

    # blech!
    else {
        return undef;
    }
}

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

    my $file = RPS::File::File->new( file_id => $self->FileID );

    my $search_data = {
        product_type       => $self->ProductType,
        upc                => $self->UPC,
        isrc               => $self->ISRC,
        artist             => $self->ArtistName,
        album              => $self->AlbumName,
        track              => $self->TrackName,
        format             => $self->FormatType,
        media_type         => $self->MediaType,
        track_num          => $self->TrackNum,
        service_id         => $file->ServiceID,
        client_product_id  => $self->ClientProductID,
        service_product_id => $self->ServiceProductID,
    };

    # returns result object
    my $matcher = RPS::Sale::Match->new( client_id => $self->{client_id}, map_type => 'auto' );
    my $result = $matcher->FindBestMatch( data => $search_data, skip_regexp_search => 1 );

    my $matches         = 0;
    my $recommendations = 0;
    if ($result) {
        $matches         = scalar @{ $result->ProductIDs };
        $recommendations = scalar @{ $result->RecProductIDs };
    }

    #print STDERR "m: $matches rec: $recommendations\n";

    if ( $args{search_only} ) {
        return
            $matches         ? $result->ProductIDs
          : $recommendations ? $result->RecProductIDs
          :                    undef;
    }

    # this sale record already has a match
    if ( $matches == 1 ) {
        $self->ImportStatus( $result->ImportStatus );
        my $new_prod_id = $result->ProductIDs->[0];
        return undef unless ( defined $new_prod_id && $result->ImportStatus );
        $self->ProductID($new_prod_id);

        if (   $self->ImportStatus == File::Sale::STATUS_MAPPED()
            || $self->ImportStatus == File::Sale::STATUS_AUTO_MAPPED()
            || $self->ImportStatus == File::Sale::STATUS_BATCH_MAPPED()
            || $self->ImportStatus == File::Sale::STATUS_DONT_MATCH() ) {
            $self->MapID( $result->MapID );
        }

        $self->Save();
        return 1;
    }

    # multi match
    elsif ( $matches > 1 || $recommendations > 0 ) {
        my @resultIDs;
        push( @resultIDs, @{ $result->ProductIDs } )    if $matches > 0;
        push( @resultIDs, @{ $result->RecProductIDs } ) if $recommendations > 0;
        return \@resultIDs;
    }

    # suggested matches
    #	elsif($recommendations > 0)
    #	{
    #		return $result->RecProductIDs;
    #	}
    # blech!
    else {
        return undef;
    }
}

# -------------------------------
# Static Methods
# -------------------------------
sub GetProductType {
    my $productType = shift;

    my %productTypes = (
        TYPE_ALBUM()          => 'Digital Album',
        TYPE_TRACK()          => 'Digital Track',
        TYPE_LP()             => 'LP',
        TYPE_LP5()            => 'LP5',
        TYPE_CD()             => 'CD',
        TYPE_VHS()            => 'VHS',
        TYPE_CASS()           => 'Cassette',
        TYPE_VCD()            => 'VCD',
        TYPE_EP()             => 'EP',
        TYPE_MD()             => 'MD',
        TYPE_DVD()            => 'DVD',
        TYPE_BLURAY()         => 'Blu-ray Disc',
        TYPE_MASTER()         => 'Master',
        TYPE_CAS_SIN()        => 'Cassette single',
        TYPE_CD_SIN()         => 'CD single',
        TYPE_DVD_CD_SET()     => 'DVD/CD set',
        TYPE_DBL_CD()         => 'Double CD',
        TYPE_LICENSE_INCOME() => 'License income',
    );

    return $productTypes{$productType};
}

sub GetFormatType {
    my $formatID = shift;

    my %formatTypes = (
        FORMAT_DOWNLOAD()        => 'Download',
        FORMAT_DOWNLOADPREMIUM() => 'Premium download',
        FORMAT_DOWNLOADUPGRADE() => 'Download upgrade',
        FORMAT_STREAM()          => 'Stream',
        FORMAT_TETHERED()        => 'Tethered download',
        FORMAT_RINGTONE()        => 'Ringtone',
        FORMAT_JUKEBOX()         => 'Jukebox',
        FORMAT_DUALDOWNLOAD()    => 'Dual download',
        FORMAT_BACKGROUNDMUSIC() => 'Background music',
        FORMAT_VPD()             => 'Variable price download',
        FORMAT_RINGBACK()        => 'Ringback',
        FORMAT_PERFORMANCE()     => 'Performance income',
        FORMAT_SYNC()            => 'Orchard Synchronization Revenue',

        FORMAT_EPHEMERAL()          => 'Performance income - Ephemeral',
        FORMAT_EPHEMERAL_WEBCAST()  => 'Performance income - Ephemeral - Webcasting',
        FORMAT_EPHEMERAL_SDARS()    => 'Performance income - Ephemeral - Satellite Radio',
        FORMAT_EPHEMERAL_BES()      => 'Performance income - Ephemeral - Business Establishment Service',
        FORMAT_EPHEMERAL_CABLERAD() => 'Performance income - Ephemeral - Cable Radio',
        FORMAT_EPHEMERAL_PES()      => 'Performance income - Ephemeral - Pre-Existing Service',
        FORMAT_EPHEMERAL_ATU()      => 'Performance income - Ephemeral - Admin True Up',
        FORMAT_EPHEMERAL_IP()       => 'Performance income - Ephemeral - International Proxies',

        FORMAT_NON_EPHEMERAL()          => 'Performance income - Non-Ephemeral',
        FORMAT_NON_EPHEMERAL_WEBCAST()  => 'Performance income - Non-Ephemeral - Webcasting',
        FORMAT_NON_EPHEMERAL_SDARS()    => 'Performance income - Non-Ephemeral - Satellite Radio',
        FORMAT_NON_EPHEMERAL_BES()      => 'Performance income - Non-Ephemeral - Business Establishment Service',
        FORMAT_NON_EPHEMERAL_CABLERAD() => 'Performance income - Non-Ephemeral - Cable Radio',
        FORMAT_NON_EPHEMERAL_PES()      => 'Performance income - Non-Ephemeral - Pre-Existing Service',
        FORMAT_NON_EPHEMERAL_ATU()      => 'Performance income - Non-Ephemeral - Admin True Up',
        FORMAT_NON_EPHEMERAL_IP()       => 'Performance income - Non-Ephemeral - International Proxies',
    );

    return $formatTypes{$formatID};
}

sub IsPerformanceIncome {
    my $formatID = shift;

    if (
           $formatID eq FORMAT_PERFORMANCE()
        ||

           $formatID eq FORMAT_EPHEMERAL()
        || $formatID eq FORMAT_EPHEMERAL_WEBCAST()
        || $formatID eq FORMAT_EPHEMERAL_SDARS()
        || $formatID eq FORMAT_EPHEMERAL_BES()
        || $formatID eq FORMAT_EPHEMERAL_CABLERAD()
        || $formatID eq FORMAT_EPHEMERAL_PES()
        || $formatID eq FORMAT_EPHEMERAL_ATU()
        || $formatID eq FORMAT_EPHEMERAL_IP()
        || $formatID eq FORMAT_EPHEMERAL_SETTLEMENT()
        ||

           $formatID eq FORMAT_NON_EPHEMERAL()
        || $formatID eq FORMAT_NON_EPHEMERAL_WEBCAST()
        || $formatID eq FORMAT_NON_EPHEMERAL_SDARS()
        || $formatID eq FORMAT_NON_EPHEMERAL_BES()
        || $formatID eq FORMAT_NON_EPHEMERAL_CABLERAD()
        || $formatID eq FORMAT_NON_EPHEMERAL_PES()
        || $formatID eq FORMAT_NON_EPHEMERAL_ATU()
        || $formatID eq FORMAT_NON_EPHEMERAL_IP()
        || $formatID eq FORMAT_NON_EPHEMERAL_SETTLEMENT()
      ) {
        return 1;
    }

    return 0;
}

sub SplitFile {
    my $self        = shift;
    my %args        = @_;
    my $file_id     = $args{file_id} || return undef;
    my $new_file_id = $args{new_file_id} || return undef;

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = qq/
        UPDATE sale
        SET file_id = ?
        WHERE 1 = 1
            AND file_id = ?
            AND (product_id IS NULL OR product_id=0) AND import_status != ?
    /;
    return $dbo->DoCmdWithPlaceholders(
        $sql,
        [
            $new_file_id,
            $file_id,
            File::Sale::STATUS_DONT_MATCH()
        ]
    );
}

sub SetConversionRate {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id} || return undef;
    my $rate    = $args{rate};
    return undef if ( !defined $rate );

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = "UPDATE sale  SET conversion_rate = ? WHERE file_id = ?";

    return $dbo->DoCmdWithPlaceholders($sql, [ $rate, $file_id ]);
}

sub SetMultipleConversionRate {
    my $self     = shift;
    my %args     = @_;
    my $file_id  = $args{file_id} || return undef;
    my $rate     = $args{rate};
    my $currency = $args{currency};
    return undef if ( !defined $rate && !defined $currency );

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = "UPDATE sale SET conversion_rate = ? WHERE file_id = ? AND currency_code = ?";
    return $dbo->DoCmdWithPlaceholders($sql, [ $rate, $file_id, $currency ]);
}

sub AggregateConversionRate {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id} || return undef;
    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = "SELECT ((select sum(units*price*conversion_rate) from sale where file_id = ?)";
    $sql .= "/(select sum(units*price) from sale where file_id = ?))";
    my $sth  = $dbo->DoCmdWithPlaceholders( $sql, [$file_id, $file_id]) ;
    my $return_aggregate_rate = $sth->fetchrow();
    return $return_aggregate_rate;
}

sub InvertUnits {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id} || return undef;
    my $country = $args{country};
    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }
    my @sqlParams = (0, $file_id);
    my $sql = "UPDATE sale SET units = units * -1 WHERE units > ? AND file_id = ?";
    if ( $country ne "" ) {
        $sql .= " AND country_code = ?";
        push @sqlParams, $country;
    }

    return $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
}

sub SetRevenue {
    my $self         = shift;
    my %args         = @_;
    my $file_id      = $args{file_id} || return undef;
    my $revenue      = $args{revenue} if ( defined( $args{revenue} ) ) || return undef;
    my $skipFreeFlag = $args{skip_free_flag};
    my $dbo;

    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my @sqlParams = ($revenue, $file_id);
    my $where = " WHERE file_id = ?";
    if ($skipFreeFlag) {
        $where .= " AND free = ?";
        push @sqlParams, 0;
    }

    my $sql = "UPDATE sale SET price = ? $where";
    return $dbo->DoCmdWithPlaceholders( $sql, \@sqlParams );
}

sub SetMultipleTerritoryRevenue {
    my $self             = shift;
    my %args             = @_;
    my $file_id          = $args{file_id} || return undef;
    my $territory        = $args{territory} || return undef;
    my $revenue          = $args{revenue} || return undef;
    my $first_reset      = $args{first_reset};
    my $negative_revenue = 0;
    my $dbo;

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

    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

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

    my $initial_revenue =
      $first_reset == 1
      ? 0
      : $file->Revenue;

    my $sql = "SELECT sum(units) FROM sale WHERE file_id = ? AND country_code = ?";
    $sql .= ' AND free = 0' if ( $args{skip_free_flag} );
    my $sth = $dbo->DoCmdWithPlaceholders( $sql, [ $file_id, $territory ] );
    my $units = $sth->fetchrow();

    #$revenue *= -1 if($negative_revenue==1);
    my $set_revenue = $revenue / $units;

    #$self->InvertUnits(dbo => $dbo, file_id => $file_id, country => $territory) if($negative_revenue==1);

    $file->Revenue( $initial_revenue + $revenue );
    $file->Save();

    $sql = "UPDATE sale SET price = ? WHERE file_id = ? AND country_code = ?";
    $sql .= ' AND free=0' if ( $args{skip_free_flag} );

    return $dbo->DoCmdWithPlaceholders( $sql, [ $set_revenue, $file_id, $territory ] );
}

sub GetFirstRankedSaleError {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id};

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = qq/
        SELECT sale_id
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
            AND map_id IS NULL
            AND import_status IN (?,?,?,?)
        ORDER BY units DESC LIMIT 1
    /;
    my $sth = $dbo->DoCmdWithPlaceholders(
        $sql,
        [
            $file_id,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetFirstSaleError {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id};

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = qq/
        SELECT MIN(sale_id) as sale_id
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
            AND map_id IS NULL
            AND import_status IN (?,?,?,?)
    /;
    my $sth = $dbo->DoCmdWithPlaceholders(
        $sql,
        [
            $file_id,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

sub GetFirstException {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $args{file_id};

    my $dbo;
    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    my $sql = qq/
        SELECT MIN(sale_id) as sale_id
        FROM sale
        WHERE 1 = 1
            AND file_id = ?
            AND import_status IN (?,?,?,?)
    /;
    my $sth = $dbo->DoCmdWithPlaceholders(
        $sql,
        [
            $file_id,
            File::Sale::STATUS_NOT_PROCESSED,
            File::Sale::STATUS_NOMATCH,
            File::Sale::STATUS_MULTIMATCH,
            File::Sale::STATUS_UNRECOVERABLE
        ]
    );
    if ( $sth && $sth->rows > 0 ) {
        my $href = $sth->fetchrow_hashref;
        return $href->{sale_id};
    } else {
        return undef;
    }
}

# get the number of records processed while file is still being imported
sub GetRecordCount {
    my $self = shift;
    my $file_id = shift || return undef;

    my $sql = "SELECT count(*) FROM sale WHERE file_id = ?";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders($sql, [$file_id]);
    return undef unless $sth;

    return $sth->fetchrow_arrayref()->[0];
}

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

    #my $sheet_num = shift || 1; unreliable when dealing with multiple sheets

    my $sql = 'SELECT * FROM ' . $self->_dbTable() . ' WHERE file_id = ? AND line_num = ? LIMIT 1';
    my $sth = $self->{dbo}->DoCmdWithPlaceholders( $sql, [ $args{file_id}, $args{line_num} ] );

    return undef unless $sth->rows;
    $self->_load( $sth->fetchrow_hashref() );

    return 1;
}

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

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

#
#
# ---------------------------------------------
# End File::Sale
# ---------------------------------------------

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

#use constant DB_TABLE => "sale";
sub _dbTable() { return 'sale'; }

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

my @allowed_sorts = qw(orig_file_name service_id);    # scottTODO

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

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

    return $self;
}

# purpose: this function will assign new period_id to all non CLOSED files
# in: the new period_id to associate unclosed files to
sub StartPeriod {
    my $self      = shift;
    my $period_id = shift;
    return undef if ( !defined $period_id || $period_id !~ /^\d+$/ );

    # update unprocessed files to be in new period
    my $sql = "UPDATE " . $self->_dbTable() . " SET period_id = ? WHERE period_id = ? - 1 AND file_status != ?";

    return $self->{dbo}->DoCmd(
        $sql,
        [
            $period_id,
            $period_id,
            File::File::STATUS_CLOSED
        ]
    );
}

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

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

    my $sql = "SELECT * FROM " . $self->_dbTable() . " WHERE file_md5sum=" . $self->{dbo}->DBQuote($md5_sum);

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

sub GetFinalSalesByPeriod {
    my $self      = shift;
    my %args      = @_;
    my $period_id = $args{period_id};
    return undef if ( !defined $period_id || $period_id !~ /^\d+$/ );

    my $sql =
        "SELECT s.* FROM "
      . $self->_dbTable()
      . " s JOIN file f ON s.file_id=f.file_id"
      . " WHERE f.period_id="
      . $self->{dbo}->DBQuote($period_id)
      . " AND f.file_status="
      . File::File::STATUS_CLOSED()
      . " AND f.type_id!=5"
      . " AND s.import_status != "
      . File::Sale::STATUS_DONT_MATCH();

    $sql .= " AND f.file_id=" . $self->{dbo}->DBQuote( $args{file_id} ) if $args{file_id};

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

sub GetFinalSalesByPeriodExtended {
    my ($self, %arg)  = @_;

    return unless defined $arg{period_id} && $arg{period_id} =~ /^\d+$/;

    my $periodID     = $self->{dbo}->DBQuote( $arg{period_id} );
    my $fileStatus   = File::File::STATUS_CLOSED(); # 5
    my $imoprtStatus = File::Sale::STATUS_DONT_MATCH(); # 8

    my $sql = qq/
        SELECT *
        FROM (
            -- track
            SELECT
                pp.title                               AS AlbumName,
                ar.name                                AS ArtistName,
                s.average_price                        AS AveragePrice,
                s.channel                              AS Channel,
                s.client_product_id                    AS ClientProductID,
                s.configuration                        AS Configuration,
                s.conversion_rate                      AS ConversionRate,
                s.country_code                         AS CountryCode,
                s.currency_code                        AS CurrencyCode,
                s.date_begin                           AS DateBegin,
                s.date_end                             AS DateEnd,
                s.file_id                              AS FileID,
                s.format_type                          AS FormatType,
                s.free                                 AS Free,
                s.import_status                        AS ImportStatus,
                m.isrc                                 AS ISRC,
                l.label_name                           AS LabelName,
                s.line_num                             AS LineNum,
                s.map_id                               AS MapID,
                t.media_type                           AS MediaType,
                s.outlet                               AS Outlet,
                s.payout_type                          AS PayoutType,
                s.price                                AS Price,
                s.price_level                          AS PriceLevel,
                s.price_type                           AS PriceType,
                s.product_id                           AS ProductID,
                s.product_type                         AS ProductType,
                s.retail                               AS Retail,
                s.retail_price                         AS RetailPrice,
                s.returns                              AS Returns,
                s.returns_revenue                      AS ReturnsRevenue,
                s.sale_id                              AS SaleID,
                s.sales                                AS Sales,
                s.sales_revenue                        AS SalesRevenue,
                s.service_id                           AS ServiceID,
                s.service_product_id                   AS ServiceProductID,
                s.total_revenue                        AS TotalRevenue,
                t.title                                AS TrackName,
                s.units                                AS Units,
                pp.upc_ean                             AS UPC,
                s.wholesale_price                      AS WholesalePrice,
                s.wholesale_rate                       AS WholesaleRate,
                ''                                     AS Distributor,
                ''                                     AS ServiceName,
                ''                                     AS ServiceNameSort,
                ar2.name                               AS AlbumArtistName,
                t.client_track_id                      AS ClientTrackID,
                t.track_order                          AS TrackNum,
                t.custom_1                             AS TrackCustom1,
                t.custom_2                             AS TrackCustom2,
                t.custom_3                             AS TrackCustom3,
                pt.disc_number                         AS DiscNumber,
                al.client_album_id                     AS ClientAlbumID,
                al.catalog_number                      AS CatalogID,
                pp.release_date                        AS ReleaseDate,
                ''                                     AS ReleaseDatePretty,
                pp.upc_alt                             AS UPCAlt,
                al.custom_1                            AS AlbumCustom1,
                al.custom_2                            AS AlbumCustom2,
                al.custom_3                            AS AlbumCustom3,
                m.duration                             AS Duration
            FROM `file`             AS f
            INNER JOIN sale         AS s  ON f.file_id = s.file_id
            INNER JOIN product      AS p  ON s.product_id = p.product_id
            INNER JOIN track        AS t  ON p.asset_id = t.track_id
            INNER JOIN artist       AS ar ON t.artist_id = ar.artist_id
            INNER JOIN `master`     AS m  ON t.master_id = m.master_id
            INNER JOIN album        AS al ON t.album_id = al.album_id
            INNER JOIN label        AS l  ON al.label_id  = l.label_id
            -- additional
            LEFT JOIN product       AS pp ON p.parent_product_id = pp.product_id AND al.album_id = pp.asset_id AND pp.product_type_id = 3
            LEFT JOIN artist        AS ar2 ON ar2.artist_id = al.artist_id
            LEFT JOIN product_track AS pt  ON pp.product_id = pt.product_id AND t.track_id = pt.track_id
            WHERE 1
                AND f.period_id = $periodID
                AND f.file_status = $fileStatus
                AND f.type_id != 5
                AND s.import_status != $imoprtStatus
                AND s.product_type = 'T'

            UNION ALL

            -- not track
            SELECT
                p.title                               AS AlbumName,
                ''                                    AS ArtistName,
                s.average_price                       AS AveragePrice,
                s.channel                             AS Channel,
                s.client_product_id                   AS ClientProductID,
                s.configuration                       AS Configuration,
                s.conversion_rate                     AS ConversionRate,
                s.country_code                        AS CountryCode,
                s.currency_code                       AS CurrencyCode,
                s.date_begin                          AS DateBegin,
                s.date_end                            AS DateEnd,
                s.file_id                             AS FileID,
                s.format_type                         AS FormatType,
                s.free                                AS Free,
                s.import_status                       AS ImportStatus,
                s.isrc                                AS ISRC,
                l.label_name                          AS LabelName,
                s.line_num                            AS LineNum,
                s.map_id                              AS MapID,
                s.media_type                          AS MediaType,
                s.outlet                              AS Outlet,
                s.payout_type                         AS PayoutType,
                s.price                               AS Price,
                s.price_level                         AS PriceLevel,
                s.price_type                          AS PriceType,
                s.product_id                          AS ProductID,
                s.product_type                        AS ProductType,
                s.retail                              AS Retail,
                s.retail_price                        AS RetailPrice,
                s.returns                             AS Returns,
                s.returns_revenue                     AS ReturnsRevenue,
                s.sale_id                             AS SaleID,
                s.sales                               AS Sales,
                s.sales_revenue                       AS SalesRevenue,
                s.service_id                          AS ServiceID,
                s.service_product_id                  AS ServiceProductID,
                s.total_revenue                       AS TotalRevenue,
                s.track_name                          AS TrackName,
                s.units                               AS Units,
                p.upc_ean                             AS UPC,
                s.wholesale_price                     AS WholesalePrice,
                s.wholesale_rate                      AS WholesaleRate,
                ''                                    AS Distributor,
                ''                                    AS ServiceName,
                ''                                    AS ServiceNameSort,
                ar.name                               AS AlbumArtistName,
                ''                                    AS ClientTrackID,
                ''                                    AS TrackNum,
                ''                                    AS TrackCustom1,
                ''                                    AS TrackCustom2,
                ''                                    AS TrackCustom3,
                ''                                    AS DiscNumber,
                al.client_album_id                    AS ClientAlbumID,
                al.catalog_number                     AS CatalogID,
                p.release_date                        AS ReleaseDate,
                ''                                    AS ReleaseDatePretty,
                p.upc_alt                             AS UPCAlt,
                al.custom_1                           AS AlbumCustom1,
                al.custom_2                           AS AlbumCustom2,
                al.custom_3                           AS AlbumCustom3,
                GROUP_CONCAT(m.duration SEPARATOR '|') AS Duration
            FROM `file`         AS f
            INNER JOIN sale     AS s  ON f.file_id = s.file_id
            INNER JOIN product  AS p  ON s.product_id = p.product_id
            INNER JOIN album    AS al ON p.asset_id = al.album_id AND p.product_type_id <> 4
            INNER JOIN artist   AS ar ON ar.artist_id = al.artist_id
            INNER JOIN label    AS l  ON al.label_id = l.label_id
            -- additional
            LEFT JOIN  track    AS t  ON al.album_id = t.album_id
            LEFT JOIN  `master` AS m  ON t.master_id = m.master_id
            WHERE 1
                AND f.period_id = $periodID
                AND f.file_status = $fileStatus
                AND f.type_id != 5
                AND s.import_status != $imoprtStatus
                AND s.product_type NOT IN ('T', 'M')
            GROUP BY s.sale_id
        ) AS tmp
        LEFT JOIN service AS s ON tmp.ServiceID = s.service_id
        ORDER BY tmp.LabelName, s.service_name, tmp.CountryCode, tmp.DateBegin, tmp.ProductType, tmp.FormatType
    /;

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

sub GetByMapID {
    my $self   = shift;
    my %args   = @_;
    my $map_id = $args{map_id} || return undef;

    my $sql = "SELECT * FROM " . $self->_dbTable() . " WHERE map_id=$map_id";

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

sub GetSalesWithQualifyingErrors {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $self->{dbo}->DBQuote( $args{file_id} ) || return undef;

    my $sql =
        "SELECT * FROM sale "
      . "LEFT JOIN sale_import_error USING (sale_id) "
      . "WHERE sale.file_id = $file_id "
      . "AND error_type != "
      . RPS::DB::Item::SaleImportError::kErrorNonqualifying . " "
      . "AND mapped = 'N' "
      . "GROUP BY sale_id ORDER BY sale_id";

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

sub GetSalesWithNonQualifyingErrors {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $self->{dbo}->DBQuote( $args{file_id} ) || return undef;

    my $sql =
        "SELECT * FROM sale "
      . "LEFT JOIN sale_import_error USING (sale_id) "
      . "WHERE sale.file_id = $file_id "
      . "AND error_type = "
      . RPS::DB::Item::SaleImportError::kErrorNonqualifying . " "
      . "GROUP BY sale_id ORDER BY sale_id";

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

sub GetUnmatchedByFile {
    my $self    = shift;
    my %args    = @_;
    my $file_id = $self->{dbo}->DBQuote(  $args{file_id} ) || return undef;

    my $sql = "SELECT * FROM " . $self->_dbTable() . " WHERE file_id=$file_id AND (product_id IS NULL OR product_id=0) ORDER BY sale_id";

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

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

    my $file_id = $args{file_id} || return undef;
    my $num = $args{num};

    my $sql = "SELECT * FROM " . $self->_dbTable() . " WHERE file_id=" . $self->{dbo}->DBQuote($file_id);
    $sql .= " LIMIT " . $self->{dbo}->DBQuote($num) if ( $num && $num > 0 );

    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 " . $self->_dbTable() . " WHERE file_status=" . $self->{dbo}->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 GetNext {
    my $self = shift;

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

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

    my $tmp = RPS::File::Sale->new( sale_id => $href->{sale_id}, dbo => $self->{dbo} );
    return undef if ( !defined $tmp || $tmp->SaleID !~ /^\d+$/ );

    return $tmp;
}

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

    my $rows;
    my $file_id = $args{file_id};
    my $dbo;

    if ( ref($self) ) {
        $dbo = $self->{dbo};
    } else {
        $dbo = $args{dbo} || Common::RSApp::GetClientDB();
    }

    if ($file_id) {
        my $sql = "DELETE FROM " . $self->_dbTable() . " WHERE file_id = ?";
        my $sth = $dbo->{DBH}->prepare($sql);
        if ($sth) {
            $rows = $sth->execute($file_id);
        }
    }

    return $rows;
}

sub GetDateRangeByFileID {
    my $self    = shift;
    my $file_id = shift || return undef;

    my @IDs = ref $file_id eq 'ARRAY' ? @$file_id : $file_id;

    my $sql = "SELECT min(date_begin), max(date_end) FROM "
        . $self->_dbTable()
        . " WHERE file_id IN (" . join(',', map{'?'} @IDs) . ")";

    my $sth = $self->{dbo}->DoCmdWithPlaceholders($sql, \@IDs);

    return undef unless $sth;
    my $row = $sth->fetchrow_arrayref;

    # Let's make sure we got something back.
    #
    if ( $row->[0] ) {
        my ( $y1, $m1, $d1 ) = split /\D/, $row->[0];
        my ( $y2, $m2, $d2 ) = split /\D/, $row->[1];

        return ( sprintf( "%d-%02d-01", $y1, $m1 ), sprintf( "%d-%02d-%02d", $y2, $m2, Date::Calc::Days_in_Month( $y2, $m2 ) ), );
    } else {
        return undef;
    }
}

sub GetRevenueSummaryByFileID {
    my $self      = shift;
    my $file_id   = shift || return undef;
    my $skip_free = shift;
    my $physical  = shift;
    my $fileType  = shift;

    my ( $units, $revenue );
    if ( $physical == 1 ) {
        $units   = 'sales-returns';
        $revenue = 'total_revenue';
    } elsif ( $physical == 2 ) {

        # These sales could be digital or physical.
        # Should be safe to just add the values for both fields,
        # since they should be blank for either the digital or physical set.
        $units   = 'sale.units + sales-returns';
        $revenue = 'ifnull(price * sale.units,0) + total_revenue';
    } else {    # physical == 0
        $units   = 'sale.units';
        $revenue = 'price * sale.units';
    }

    my %dist_fee     = ();
    my $has_dist_fee = 0;
    my $dist_fee_sql = "select associated_format,dist_fee_pct from user_input_dist_fee where file_id = ?";
    my $tth          = $self->{dbo}->DoCmdWithPlaceholders($dist_fee_sql, [$file_id]);
    while ( my $row = $tth->fetchrow_arrayref ) {
        $dist_fee{ $row->[0] } = $row->[1];
        $has_dist_fee = 1 if ( $row->[1] > 0 );

    }

    my @sqlParams = ($file_id);
    my $sql =
        "SELECT if(import_status = "
      . File::Sale::STATUS_DONT_MATCH()
      . ", 'dontmatch',if(product_id is null, 'unmatched', 'matched')) as stat, ";
    $sql .= "sum(($revenue) * sale.conversion_rate), sum($units), count(*)";
    $sql .= ",sale.format_type" if ( $has_dist_fee == 1 );
    $sql .= " FROM file,sale WHERE ";
    $sql .= "file.file_id = ? and sale.file_id=file.file_id ";

    # We only want to include sale files that are currently associated with a license income line.
    #
    if ( $fileType == File::File::FILETYPE_LICENSE_INCOME() ) {
        $sql .= "AND sale.sale_id IN (SELECT sale_id FROM license_income WHERE file_id = ?) ";
        push @sqlParams, $file_id
    }

    $sql .= "AND sale.free=0 "  if ($skip_free);
    $sql .= "GROUP BY sale.file_id,stat";
    $sql .= ",sale.format_type" if ( $has_dist_fee == 1 );

    my $sth = $self->{dbo}->DoCmdWithPlaceholders($sql, \@sqlParams);
    return undef unless $sth;

    my ( %summary, %summed );
    while ( my $row = $sth->fetchrow_arrayref ) {
        $row->[1] -= ( $row->[1] * ( $dist_fee{ $row->[4] } * .01 ) ) if ( $has_dist_fee == 1 && $dist_fee{ $row->[4] } > 0 );
        if ( $row->[0] eq "dontmatch" ) {
            $summed{dontmatch}{revenue} += $row->[1];
            $summed{dontmatch}{units}   += $row->[2];
            $summed{dontmatch}{lines}   += $row->[3];
        } elsif ( $row->[0] eq "unmatched" ) {
            $summed{unmatched}{revenue} += $row->[1];
            $summed{unmatched}{units}   += $row->[2];
            $summed{unmatched}{lines}   += $row->[3];
        } elsif ( $row->[0] eq "matched" ) {
            $summed{matched}{revenue} += $row->[1];
            $summed{matched}{units}   += $row->[2];
            $summed{matched}{lines}   += $row->[3];
        }
    }
    if ( $summed{dontmatch}{lines} > 0 ) {
        $summary{dontmatch} = {
            revenue => $summed{dontmatch}{revenue},
            units   => $summed{dontmatch}{units},
            lines   => $summed{dontmatch}{lines},
        };
    }
    if ( $summed{unmatched}{lines} > 0 ) {
        $summary{unmatched} = {
            revenue => $summed{unmatched}{revenue},
            units   => $summed{unmatched}{units},
            lines   => $summed{unmatched}{lines},
        };
    }
    if ( $summed{matched}{lines} > 0 ) {
        $summary{matched} = {
            revenue => $summed{matched}{revenue},
            units   => $summed{matched}{units},
            lines   => $summed{matched}{lines},
        };
    }

    $sql = "SELECT SUM($units) FROM sale WHERE file_id = ? AND free = ?";
    $sth = $self->{dbo}->DoCmdWithPlaceholders($sql, [$file_id, 1]);
    return undef unless $sth;

    while ( my $row = $sth->fetchrow_arrayref ) {
        $summary{free} = { units => $row->[0] };
    }

    return \%summary;
}


1;