package BookPub::Sale::Report::Royalty::Macmillan;

use strict;
use Template;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Log;
use Common::Client;
use Common::Email;
use Common::DB::ItemCollection;
use Common::DB::Item;
use Common::Util qw(formatFixedPoint);

use lib '/app/tools/bookpub/lib';
use BookPub::DB::Item::BookProduct;
use BookPub::DB::Item::ClientService;
use BookPub::DB::Item::Period;
use BookPub::DB::Item::PriceTypeQualifier;
use BookPub::DB::Item::Sale;
use BookPub::DB::Item::File;
use BookPub::DB::Item::Service;
use BookPub::Tracker::Service;

use base 'BookPub::Sale::Report::Royalty';

use constant kDB         => Common::DB::Item::kClientDB;
use constant OUTDIR_BASE => '/app/data/sale_report/tmp/';


my $_sale_file_version = '';

my %HEADER_MAIN = (
    _order => [
        qw/
          doc_type
          san
          cust_ref
          isbn
          quantity
          price
          discount1
          comments
          discount2
          freight
          tax
          user
          date
          promo_code
          name
          address1
          address2
          address3
          city
          state
          postal_code
          country_code
          purchase_price
          revenue
          cn_reason
          comment2
          comment3
          comment4
          comment5
          comment6
          comment7
          comment8
          comment9
          comment10
          /
    ],
    doc_type       => 'Doc Type(4)',
    san            => 'Ship To/SAN(10)',
    cust_ref       => 'Cust Ref(14)',
    isbn           => 'ISBN/EAN(20)',
    quantity       => 'Demand Qty(7)',
    price          => 'Price(8.2)',
    discount1      => 'Discount-%(3.2)',
    comments       => 'Comment(40)',
    discount2      => 'Discount+%(3.2)',
    freight        => 'Freight DTC(10.2)',
    tax            => 'tax DTC(10.2)',
    user           => 'user(3)',
    date           => 'bookseller date(yyyy-mm-dd)',
    promo_code     => 'Promo Code(10)',
    name           => 'Name(30)',
    address1       => 'Address-1(30)',
    address2       => 'Address-2(30)',
    address3       => 'Address-3(30)',
    city           => 'City(30)',
    state          => 'St(2)',
    postal_code    => 'Zip(12)',
    country_code   => 'Country_Code(2)',
    purchase_price => 'OV-Restrict(1)',
    revenue        => '1-time Country(2)',
    cn_reason      => 'C/N-Reason(2)',
    comment2       => 'comment-2(40)',
    comment3       => 'comment-3(40)',
    comment4       => 'comment-4(40)',
    comment5       => 'comment-5(40)',
    comment6       => 'comment-6(40)',
    comment7       => 'comment-7(40)',
    comment8       => 'comment-8(40)',
    comment9       => 'comment-9(40)',
    comment10      => 'comment-10(40)',
);

my @MONTHS = qw(
    January February March April May June
    July August September October November December
);

sub new {
    my $class = shift;

    my $self = {};
    bless $self, $class;

    $self->_init(@_);

    return $self;
}

sub fileName {
    my ($self) = @_;
    if ( !$self->{_fileName} ) {
        my $period = BookPub::DB::Item::Period->Lookup( period_id => $self->{_periodID} );
        my $periodRef = $period->name();
        $periodRef =~ s/\s+/_/g;
        $periodRef =~ s/\W+//g;
        $periodRef =~ s/__/_/g;
        $periodRef ||= $period->period_id();

        $self->{_fileName} = "Macmillan_RoyaltyReport_$periodRef.csv";
    }
    return $self->{_fileName};
}

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

    $self->SUPER::_init(%args);

    $self->{clientID} = $args{clientID} ? $args{clientID} : Common::RSApp::GetClientID();
    $self->{tmplObj} = Template->new( { START_TAG => '{', END_TAG => '}' } );

}

my %serviceNames;

sub create {
    my $self = shift;
    my %tmplVars;

    # We'll keep a separate tally of units and revenue and
    # return those at the end for validation.
    my $totalUnits;
    my $totalRevenueUnrounded;

    my $periodID   = $self->periodID();
    my $period     = BookPub::DB::Item::Period->Lookup( period_id => $periodID );
    my $periodName = $period->name;

    my $sales = BookPub::DB::Item::Sale->GetFinalSalesByPeriod( $self->periodID() );

    my $outFilePath = $self->filePath();

    unless ( open( OUT, '>:utf8', $outFilePath ) ) {
        die "can't open output file ($outFilePath) - $!\n";
    }

    # print the main header
    my @headers = @HEADER_MAIN{ @{ $HEADER_MAIN{_order} } };
    print OUT join( ",", @headers ), "\n";

    my %rows;
    my %fileNames;
    my %custRefs;
    my $periodURL = "https://macmillanus.royaltyshare.com/bookpub/tracker?period=" . $periodID;

    # There are some sales in CAD that we are not going to conver to USD.
    # We will use the SAN to determine this.
    my %dontConvert = (
        "418891" => 1,    # Amazon MMHQQ CAD
        "418892" => 1,    # Google CAD
        "369605" => 1,    # Kobo CAD
        "421524" => 1,    # ebooks.com CAD
        "422552" => 1,    # Glose CAD
        "422454" => 1,    # Perusall Trade CAD
        "422461" => 1,    # Perusall Learning CAD
        "424669" => 1,    # Bibliotheca CAD (now named as OCLC)
        "424739" => 1,    # De Marque Trade Agency CAD
        "424729" => 1,    # De Marque Library CAD
        "425935" => 1,    # Hoopla Digital CAD
        "421274" => 1,    # Rally Reader CAD
        "429801" => 1,    # Speechify EBOK CAD
        "427828" => 1,    # Speechify AUDB CAD
        "432442" => 1,    # Libro.fm AUDB CAD
        "432688" => 1,    # Legible EBOK CAD
        "427573" => 1,    # VitalSource EBOK CAD
        "420420" => 1,    # Amazon V66
    );

    while ( my $sale = $sales->next() ) {
        my $fileID = $sale->file_id;
        my $saleID = $sale->sale_id;

        my $units   = $sale->units;
        my $revenue = $sale->revenue;

        my $currencyCode = $sale->currency_code;
        my $productType  = $sale->product_type;
        my $countryCode  = $sale->country_code;

        # Since we're going to change US territory country codes to US,
        # let's stow away the real country code for use later.
        my $realCountryCode = $countryCode;

        if ( $sale->service_id == BookPub::Tracker::Service::OPEN_ROAD_MEDIA() ) {
            $countryCode = 'US';
        }

        $countryCode = $self->_normalizeCountryCode($countryCode);

        # Fetch/store the service name
        my $serviceID = $sale->service_id;
        my $serviceName;

        if ( $serviceNames{$serviceID} ) {
            $serviceName = $serviceNames{$serviceID};
        } else {
            my $service = BookPub::DB::Item::Service->Lookup( service_id => $serviceID );
            $serviceName = $service->service_name;
            $serviceNames{$serviceID} = $serviceName;
        }

        # Fetch/store the file name
        my $fileName;

        if ( $fileNames{$fileID} ) {
            $fileName = $fileNames{$fileID};
        } else {
            my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );
            $fileName = $self->_normalizeFileName( $file->orig_file_name );
            $fileNames{$fileID} = $fileName;
        }

        # We don't do anything with %sanCriteria here.  That's for the validation check below.
        my ( $clientService, %sanCriteria ) = $self->_getClientService( $sale, $countryCode, $fileName );

        #  If we don't have one, time to error out.
        if ( !$clientService ) {
            my $error = "No match found in client service table for sale id " . $sale->sale_id;
            $self->_sendErrorEmail($error);
            die $error . "\n";
        }

        my $customerSAN = $clientService->client_service_id;

        my $fee = 0;

        my $conversionRate   = $sale->conversion_rate;
        my $convertedRevenue = $revenue;

        if ( $currencyCode ne 'USD' ) {
            if ($conversionRate) {
                $convertedRevenue *= $conversionRate;
                $fee              *= $conversionRate;

                # We are no longer converting the CAD calues for some SANs
                if ( !$dontConvert{$customerSAN} ) {
                    $revenue *= $conversionRate;
                }
            }
        }

        $totalUnits += $sale->units;
        $totalRevenueUnrounded += $convertedRevenue;

        my $productID = $sale->product_id;

        my $bookProduct = BookPub::DB::Item::BookProduct->Lookup( product_id => $productID );
        my $isbn = $bookProduct->isbn13;
        if ( !$isbn ) {
            $isbn = $bookProduct->isbn10;
        }

        # Absolute values for everything!
        my $listPrice     = abs( $sale->list_price );
        my $purchasePrice = abs( $sale->r_purchase_price );

        if (   $sale->service_id == BookPub::Tracker::Service::OPEN_ROAD_MEDIA()
            || $sale->service_id == BookPub::Tracker::Service::PLAYAWAY()
            || ( $serviceID == BookPub::Tracker::Service::LSI() && $_sale_file_version eq 'lsi_2' )
        ) {
            $purchasePrice = 'Y';
        }

        # We need to make sure the list price is in USD
        my $listPriceCurrency = $sale->r_list_price_currency;
        if ( !$listPriceCurrency ) {
            $listPriceCurrency = $currencyCode;
        }

        # If it's not USD, let's convert it.
        # !!! Unless it's one of the CAD SANs that we no longer convert.
        if ( $listPriceCurrency ne 'USD' && !$dontConvert{$customerSAN} ) {
            if ( $listPriceCurrency eq $currencyCode ) {
                $listPrice *= $conversionRate;
                $listPrice = formatFixedPoint( $listPrice, 2 );
            } else {

                # Well, we can't convert this so let's throw an error.
                my $error = "Unable to convert list price to USD - sale id:" . $saleID;

                $self->_sendErrorEmail($error);

                die "Unable to convert list price to USD - sale id: $saleID\n";
            }
        }

        # We can't trust the discount field in the sale table.
        # We need to figure out the real discount number.
        my $price;
        if ($units) {
            $price  = abs( $revenue / $units );
        }

        my $discount = 0;

        # This shouldn't happen, but let's check for it just in case.
        if ( ( !$listPrice || $listPrice == 0 ) && ( $revenue != 0 || $fee != 0 ) && $sale->is_free eq 'N' ) {
            my $error = "Missing list price for non-free sale with revenue/fee. Sale ID: $saleID";

            $self->_sendErrorEmail($error);

            die "Missing list price for non-free sale with revenue/fee. Sale ID: $saleID\n";
        }

        # Need to trim this to two decimal places.
        $listPrice = formatFixedPoint( $listPrice, 2 );

        # If it's free, then we can just hard code the discount rate.
        if ( $sale->is_free eq 'Y' || $revenue == 0 ) {
            $discount = 100;
        } elsif ( $listPrice && $listPrice != 0 ) {
            my $discountRate = 100 * ( 1 - ( $price / $listPrice ) );
            $discount = formatFixedPoint( $discountRate, 2 );
        }

        # We only want discount rates with a value between 0 and 100
        #
        if ( $discount > 100 || $discount < 0 ) {
            $discount = 0;
            $listPrice = formatFixedPoint( $price, 2 );
        }

        if (
               $serviceID == BookPub::Tracker::Service::OPEN_ROAD_MEDIA()
            || $serviceID == BookPub::Tracker::Service::PLAYAWAY()
        ) {
            $discount = 0;
        } elsif ( $serviceID == BookPub::Tracker::Service::LSI() ) {
            # LSI V2 (RSD-11770 and RSD-11771)
            if ( $fileName =~ /^sales[ _]comp[ _]LSI[ _]/i ) {
                $discount = $sale->r_discount * 100;
            }

        }

        my $docType;
        my $addlDocType;

        if ( $units == 0 ) {
            next;
        } elsif ( $units < 0 ) {
            $docType     = 'CEBO' if ( $revenue != 0 || $sale->is_free eq 'Y' );
            $addlDocType = 'IAPI' if $fee != 0;
        } elsif ( $units > 0 && ( $revenue - $fee == 0 || $sale->is_free eq 'Y' ) ) {
            $docType = 'GEBO';
        } elsif ( $units > 0 && ( $revenue > 0 || $listPrice > 0 ) ) {
            $docType = 'IEBO' if ( $revenue > 0 || ( $listPrice > 0 && $fee == 0 ) );
            $addlDocType = 'CAPI' if $fee != 0;
        }

        if ( $sale->service_id == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION() ) {

            my $file_version = BookPub::DB::Item::File->Lookup( file_id => $fileID )->version_num();

            if ( $file_version == 17 ) {
                $docType = "IAML" if ( $docType eq "IEBO" && $sale->r_transaction_category =~ /^includED/i );
                $docType = "CAML" if ( $docType eq "CEBO" && $sale->r_transaction_category =~ /^includED/i );
            }

            if ( $file_version == 18 || $file_version == 19 ) {
                $docType = "IAML" if $docType eq "IEBO";
                $docType = "CAML" if $docType eq "CEBO";
            }
        }

        if ( $sale->service_id == BookPub::Tracker::Service::VIRDOCS() ) {
            $docType = "IAML" if ( $docType eq "IEBO" && $sale->r_channel =~ /inclusive/i );
            $docType = "CAML" if ( $docType eq "CEBO" && $sale->r_channel =~ /inclusive/i );
            $docType = "IEML" if ( $docType eq "IEBO" && $sale->r_channel =~ /equitable/i );
            $docType = "CEML" if ( $docType eq "CEBO" && $sale->r_channel =~ /equitable/i );
        }

        if ( $sale->service_id == BookPub::Tracker::Service::OPEN_ROAD_MEDIA() ) {
            $docType = "CEBO" if ( $sale->r_revenue < 0 );
            $docType = "IEBO" if ( $sale->r_revenue >= 0 );
        }

        $_sale_file_version = '';
        # AMAZON V66 (RSD-11604)
        if ( $sale->service_id == BookPub::Tracker::Service::AMAZON() ) {
            my $fileVersion = BookPub::DB::Item::File->Lookup( file_id => $fileID )->version_num();
            if ( $fileVersion == 66 ) {
                $_sale_file_version = 'amazon_66';
                $docType     = "IRBO" if $units != 0 || $revenue != 0 || $sale->is_free eq 'Y';
                $addlDocType = 'CMBO' if $sale->r_fee != 0;

                my $discountRate = 100 * (1 - $sale->r_cogs);
                $discount = formatFixedPoint( $discountRate, 2 );

                my $rFeeConverted = $sale->r_fee;
                if ( $conversionRate ) {
                    if ( !$dontConvert{$customerSAN} ) {
                        $rFeeConverted = $sale->r_fee * $conversionRate;
                    }
                    my $unitFreeDiscountRate = 1 - $sale->r_comments / 100;
                    $fee = $rFeeConverted / $sale->r_units * $unitFreeDiscountRate;
                }
            }
        }
        # LSI V2 (RSD-11770 and RSD-11771)
        if ( $sale->service_id == BookPub::Tracker::Service::LSI() ) {
            my $fileVersion = BookPub::DB::Item::File->Lookup( file_id => $fileID )->version_num();
            if ( $fileVersion == 2 ) {
                $_sale_file_version = 'lsi_2';

                if ( $fileName =~ /^sales[ _]comp[ _]LSI[ _]/i ) {
                    $docType     = "IRBO" if $units != 0 || $revenue != 0 || $sale->is_free eq 'Y';
                    $addlDocType = 'CMBO' if $sale->r_fee != 0;
                }

                my $rFeeConverted = $sale->r_fee;
                if ( $conversionRate ) {
                    if ( !$dontConvert{$customerSAN} ) {
                        $rFeeConverted = $sale->r_fee * $conversionRate;
                    }
                    $fee = $sale->r_units ? $rFeeConverted / $sale->r_units : 0;
                }
            }
        }

        if ( !( $docType || $addlDocType || ( $units < 0 && $revenue - $fee == 0 ) ) ) {
            my $error = "No doctype set for sale: " . $sale->sale_id;

            $self->_sendErrorEmail($error);

            die "No doctype set for sale: " . $sale->sale_id . "\n";
        }

        # Now that we've set the doctype, we need to use the absolute value of the units and revenue for the report.
        # The doctype will tell them if it's positive or negative.
        $units     = abs($units);
        $revenue   = abs($revenue);
        $fee       = abs($fee);

        my $saleDate = $sale->date_end;

        # Cust Ref column
        my $custRef;
        my $sanError;

        if ( $custRefs{$fileID} ) {
            $custRef = $custRefs{$fileID};
        } else {
            ( $custRef, $sanError ) = $self->_getCustRef( $serviceID, $fileName, $saleDate, $fileID );

            if ($sanError) {
                $self->_sendErrorEmail($sanError);
                die $sanError . "\n";
            }

            $custRefs{$fileID} = $custRef;
        }

        # if this is a Higher Education file, add an HE to the customer ref
        if ( $serviceID == BookPub::Tracker::Service::VITAL_SOURCE() ) {
            if ( $fileName =~ /Higher Education/i ) {
                $custRef .= "HE";
            }
        } elsif ( $serviceID == BookPub::Tracker::Service::BARNES_NOBLE_EDUCATION() ) {
            $custRef .= "HE" if $fileName !~ /trade/i;
        } elsif ( $serviceID == BookPub::Tracker::Service::PERUSALL() ) {
            # for RS file format
            if ( $fileName =~ m/Perusall[ _-](Trade|Learning)[ _-]IA[ _-]Sales[ _-](\d{4})[ _-](\d{2})/i ) {
                $custRef .= "HE" if $1 =~ /learning/i;
            } else {
                $custRef .= "HE";
            }
        }

        # Previously, the same cust ref had been used for every sale in a file.
        # Now we are finding that we may need to use multiple cust refs for one file.
        # So, we'll have to check the sale data here and modify it as needed.
        if ( $serviceID == BookPub::Tracker::Service::YUZU() ) {
            if ( $sale->r_comments == 100024 ) {
                $custRef .= "HE";
            }
        }

        my ( $custRef, $custRefError ) = $self->_getSaleCustRef( $serviceID, $sale, $fileName, $custRef );

        if ($custRefError) {
            $self->_sendErrorEmail($custRefError);
            die $custRefError . "\n";
        }

        # Comments column (not to be confused with r_comments!)
        my ( $comments, $commentsError ) = $self->_getComments( $sale, $countryCode, $fileName, $custRef );

        # Comment2 column
        my $comment2 = $self->_getComment2( $period, $sale );

        # Comment4 column
        my $comment4 = $self->_getComment4( $serviceID, $sale, $fileName );

        # Comment 5
        my $comment5 = $self->_getComment5( $serviceID, $sale, $fileName );

        # Comment 6
        my $comment6 = $self->_getComment6( $serviceID, $sale, $fileName );

        # Comment 7
        my $comment7 = $self->_getComment7( $serviceID, $sale, $fileName );

        if ($commentsError) {
            if ( $commentsError eq 'Country Code' ) {
                $commentsError = "Unable to find country code in file name " . $fileName;
            } elsif ( $commentsError eq "Comments Length" ) {
                $commentsError =
                  "Comment text '" . $comments . "' has exceeded the 40 character limit for that field - sale id " . $sale->sale_id;
            }

            $self->_sendErrorEmail($commentsError);
            die $commentsError . "\n";
        }

        # OPEN_ROAD_MEDIA or Amazon V66 or PLAYAWAY or Audbile quarterly files
        unless (
            $comments =~ /^Open Road/i
            || $_sale_file_version eq 'amazon_66'
            || $serviceID == BookPub::Tracker::Service::PLAYAWAY()
            || ( $serviceID == BookPub::Tracker::Service::LSI() && $_sale_file_version eq 'lsi_2' )
            || ( $serviceID == BookPub::Tracker::Service::AUDIBLE() && $custRef =~ /Q\d \d{4}/ )
        ) {
            $custRef .= "-$countryCode";
        }

        my $priceType = BookPub::DB::Item::PriceType->Lookup( price_type_id => $sale->price_type_id )->display;
        my $purchaseType = BookPub::DB::Item::Sale->GetPurchaseTypeString( $sale->purchase_type );

        if ($docType) {
            $rows{$customerSAN}{$docType}{$custRef}{$isbn}{$realCountryCode}
            {$listPrice}{$discount}{$comments}{$purchasePrice}
            {$comment2}{$comment4}{$comment5}{$comment6}{$comment7}
            {units} += $units;

            $rows{$customerSAN}{$docType}{$custRef}{$isbn}{$realCountryCode}
            {$listPrice}{$discount}{$comments}{$purchasePrice}
            {$comment2}{$comment4}{$comment5}{$comment6}{$comment7}
            {revenue} += $revenue;
        }

        if ($addlDocType) {
            my $formattedFee = formatFixedPoint( $fee, 2 );
            $rows{$customerSAN}{$addlDocType}{$custRef}{$isbn}{$realCountryCode}
            {$formattedFee}{"0.00"}{$comments}{$formattedFee}
            {$comment2}{$comment4}{$comment5}{$comment6}{$comment7}
            {units} += $units;

            $rows{$customerSAN}{$addlDocType}{$custRef}{$isbn}{$realCountryCode}
            {$formattedFee}{"0.00"}{$comments}{$formattedFee}
            {$comment2}{$comment4}{$comment5}{$comment6}{$comment7}
            {revenue} += $fee;
        }
    }

    my $count = 0;

    foreach my $customerSAN ( sort keys %rows ) {
        my $customerSANsales = $rows{$customerSAN};

        foreach my $docType ( sort keys %$customerSANsales ) {
            my $docTypeSales = $customerSANsales->{$docType};

            foreach my $custRef ( sort keys %$docTypeSales ) {
                my $custRefSales = $docTypeSales->{$custRef};

                foreach my $isbn ( sort keys %$custRefSales ) {
                    my $isbnSales = $custRefSales->{$isbn};

                    foreach my $countryCode ( sort keys %$isbnSales ) {
                        my $countryCodeSales = $isbnSales->{$countryCode};

                        foreach my $listPrice ( sort keys %$countryCodeSales ) {
                            my $listPriceSales = $countryCodeSales->{$listPrice};

                            foreach my $discount ( sort keys %$listPriceSales ) {
                                my $discountSales = $listPriceSales->{$discount};

                                foreach my $comments ( sort keys %$discountSales ) {
                                    my $commentsSales = $discountSales->{$comments};

                                    foreach my $purchasePrice ( sort keys %$commentsSales ) {
                                        my $purchasePriceSales = $commentsSales->{$purchasePrice};

                                        foreach my $comment2 ( sort keys %$purchasePriceSales ) {
                                            my $hComment2 = $purchasePriceSales->{$comment2};

                                            foreach my $comment4 ( sort keys %$hComment2 ) {
                                                my $hComment4 = $hComment2->{$comment4};

                                                foreach my $comment5 ( sort keys %$hComment4 ) {
                                                    my $hComment5 = $hComment4->{$comment5};

                                                    foreach my $comment6 ( sort keys %$hComment5 ) {
                                                        my $hComment6 = $hComment5->{$comment6};

                                                        foreach my $comment7 ( sort keys %$hComment6 ) {
                                                            my $row = $hComment6->{$comment7};

                                                            $tmplVars{doc_type}       = $docType;
                                                            $tmplVars{san}            = $customerSAN;
                                                            $tmplVars{cust_ref}       = $custRef;
                                                            $tmplVars{isbn}           = $isbn;
                                                            $tmplVars{quantity}       = $row->{units};
                                                            $tmplVars{price}          = $listPrice;
                                                            $tmplVars{discount1}      = $discount;
                                                            $tmplVars{comments}       = $comments;
                                                            $tmplVars{discount2}      = '';
                                                            $tmplVars{freight}        = '';
                                                            $tmplVars{tax}            = '';
                                                            $tmplVars{user}           = '';
                                                            $tmplVars{date}           = '';
                                                            $tmplVars{promo_code}     = '';
                                                            $tmplVars{name}           = '';
                                                            $tmplVars{address1}       = '';
                                                            $tmplVars{address2}       = '';
                                                            $tmplVars{address3}       = '';
                                                            $tmplVars{city}           = '';
                                                            $tmplVars{state}          = '';
                                                            $tmplVars{postal_code}    = '';
                                                            $tmplVars{country_code}   = $countryCode;

                                                            # RSD-11230 - MMUS wants these columns to be blank now.
                                                            # $tmplVars{purchase_price} = $purchasePrice;
                                                            # $tmplVars{cn_reason}      = $count ? '' : $periodURL;
                                                            $tmplVars{purchase_price} = $purchasePrice // '';

                                                            $tmplVars{cn_reason}      = '';

                                                            # RSD-11431 - MMUS wants this column populated again.
                                                            $tmplVars{revenue}        = formatFixedPoint( $row->{revenue}, 2 );

                                                            $tmplVars{comment2}       = $comment2;
                                                            $tmplVars{comment3}       = 'CTRCD;' . $countryCode;
                                                            $tmplVars{comment4}       = $comment4;
                                                            $tmplVars{comment5}       = $comment5;
                                                            $tmplVars{comment6}       = $comment6;
                                                            $tmplVars{comment7}       = $comment7;
                                                            $tmplVars{comment8}       = '';
                                                            $tmplVars{comment9}       = '';
                                                            $tmplVars{comment10}      = '';

                                                            if ( $_sale_file_version eq 'amazon_66' ) {
                                                                $tmplVars{comments}       = ''                          if $docType eq 'IRBO';
                                                                $tmplVars{comments}       = 'POD MANUFACTURING CHARGES' if $docType eq 'CMBO';
                                                                $tmplVars{country_code}   = '';
                                                                $tmplVars{purchase_price} = 'Y';
                                                                $tmplVars{revenue}        = '';
                                                                $tmplVars{comment2}       = '';
                                                                $tmplVars{comment3}       = '';
                                                                $tmplVars{comment4}       = '';
                                                            } elsif ( $_sale_file_version eq 'lsi_2' ) {
                                                                $tmplVars{comments}       = $comments                   if $docType eq 'IRBO';
                                                                $tmplVars{comments}       = 'POD MANUFACTURING CHARGES' if $docType eq 'CMBO';
                                                                $tmplVars{purchase_price} = 'Y';
                                                                $tmplVars{cn_reason}      = 'AR'                        if $docType eq 'CMBO';
                                                            }

                                                            my @detailRow = @tmplVars{ @{ $HEADER_MAIN{_order} } };
                                                            print OUT join( ",", @detailRow ), "\n";
                                                            $count++;
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    close(OUT);

    # Going to send everything back in a hash so that it's
    # easy to add more stuff in later.
    #
    my %returnValues;
    $returnValues{totalUnits}            = $totalUnits;
    $returnValues{totalRevenueUnrounded} = $totalRevenueUnrounded;

    return (%returnValues);

}

sub _normalizeCountryCode {
    my ( $self, $countryCode ) = @_;

    # We want to change the country code for US territories back to US.
    if (   $countryCode eq 'AS'
        || $countryCode eq 'FM'
        || $countryCode eq 'GU'
        || $countryCode eq 'MH'
        || $countryCode eq 'MP'
        || $countryCode eq 'PR'
        || $countryCode eq 'PW'
        || $countryCode eq 'VI' ) {
        $countryCode = 'US';
    }

    return $countryCode;
}

sub _getComment2 {
    my ($self, $period, $sale) = @_;

    my $comment2 = $period->name;

    if ( $sale->service_id == BookPub::Tracker::Service::AUDIBLE() ) {
        $comment2 = $sale->r_comments;
    }

    return $comment2;
}

sub _getComment4 {
    my ($self, $serviceID, $sale, $fileName) = @_;

    my $comment4 = '';

    if ( $serviceID == BookPub::Tracker::Service::IN_AUDIO && $fileName =~ /Digital Royalty/i ) {
        $comment4 = $sale->r_channel
    } elsif ( $serviceID == BookPub::Tracker::Service::BARNES_NOBLE_EDUCATION
              || $serviceID ==  BookPub::Tracker::Service::VITAL_SOURCE
    ) {
        $comment4 = $sale->r_comments;
    } elsif ( $serviceID == BookPub::Tracker::Service::LSI && $_sale_file_version eq 'lsi_2' ) {
        ($comment4) = $fileName =~ /^sales[ _]comp[ _]LSI[ _](?:GLOBAL|POD[ _]Wholesale)[ _][A-Z]{3}[ _](\d{7})[ _]\d{4}[ _]\d{2}/i;
    }

    return $comment4;
}

sub _getComment5 {
    my ($self, $serviceID, $sale, $fileName) = @_;

    my $comment5 = '';

    if ( $serviceID == BookPub::Tracker::Service::LSI && $_sale_file_version eq 'lsi_2') {
        $comment5 = $sale->r_client_product_id;
    }

    return $comment5;
}

sub _getComment6 {
    my ($self, $serviceID, $sale, $fileName) = @_;

    my $comment6 = '';

    if ( $serviceID == BookPub::Tracker::Service::LSI && $_sale_file_version eq 'lsi_2' ) {
        $comment6 = $sale->country_code;
    }

    return $comment6;
}

sub _getComment7 {
    my ($self, $serviceID, $sale, $fileName) = @_;

    my $comment7 = '';

    if ( $serviceID == BookPub::Tracker::Service::LSI && $_sale_file_version eq 'lsi_2' ) {
        $comment7 = $sale->r_comments;
    }

    return $comment7;
}

sub _getComments {
    my ( $self, $sale, $countryCode, $fileName, $custRef ) = @_;

    my $comments;
    my $error;

    my $serviceID   = $sale->service_id;
    my $serviceName = $serviceNames{$serviceID};

    if (   $serviceID == BookPub::Tracker::Service::KOBO()
        && $countryCode ne 'US'
        && ( $fileName =~ /US/ || $fileName =~ /^MACMILLAN(-|~)MACMILLAN(-|~)/i ) ) {

        # For Kobo, all non-US sales in the US file will be marked as "WW" for World-wide.
        $comments = "Kobo WW Sales for " . $custRef;
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE() && $fileName =~ /US/ ) {
        my $revenuePeriod = $custRef;

        # Need to get rid of some stuff we added for the cust ref column.
        $revenuePeriod =~ s/HE//;

        if ( $fileName =~ /K-12/ ) {
            $comments = "Google US K-12 Sales for " . $revenuePeriod;
        } else {

            # The Google US file contains US territories in the country field.  Let's just mark them all as "US".
            $comments = "Google US Sales for " . $revenuePeriod;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE() && $fileName =~ /CA/ ) {
        my $revenuePeriod = $custRef;

        # Need to get rid of some stuff we added for the cust ref column.
        $revenuePeriod =~ s/HE//;

        if ( $fileName =~ /K-12/ ) {
            $comments = "Google CA K-12 Sales for " . $revenuePeriod;
        } else {
            $comments = "Google CA Sales for " . $revenuePeriod;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE() && $fileName =~ /World/ ) {

        # Filing all of the sales in the Google World file under "WW".
        $comments = "Google WW Sales for " . $custRef;
    } elsif ( $serviceID == BookPub::Tracker::Service::LSI() ) {

        if ( $_sale_file_version eq 'lsi_2' ) {
            $comments = $sale->r_distributor;
        } else {
            # Note that we are using a shortened version of the service name here,
            # to avoid going over the max size for this field.
            $comments = "Ingram LSI " . $countryCode;

            # Need to add a "W" if this is a wholesale file.
            if ( $fileName =~ /Wholesale/i ) {
                $comments .= " W";
            }

            my $revenuePeriod = $custRef;

            # Also need to remove the "W" from the cust ref, since we
            # want it to show up somewhere else in the string.
            $revenuePeriod =~ s/W//;

            $comments .= " Sales for " . $revenuePeriod;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::APPLE() ) {

        # We need to grab the country code from the file name.
        if ( $fileName =~ / (\D{2})(?:-split)?\./i || $fileName =~ /[\s_-]\d{4}[\s_-](\D{2})[\s_-]/i ) {
            $comments = "Apple " . $1 . " Sales for " . $custRef;
        } else {
            $error = "Country Code";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::AMAZON() ) {

        # For Amazon, we need to figure out the feed ID and the country code.
        # Also, get rid of the country code that might be tacked on to the end of the Cust Ref.
        my $revenuePeriod = $custRef;

        # Need to get rid of some stuff we added for the cust ref column.
        $revenuePeriod =~ s/HEREN//;
        $revenuePeriod =~ s/HE//;

        my $fileCountry;
        if ( $revenuePeriod =~ /-(\D{2})$/ ) {
            $fileCountry = $1;
        } else {
            $fileCountry = 'US';
        }

        $revenuePeriod =~ s/-\D{2}//;

        my $feedID;
        if ( $fileName =~ /^(\w{5})[\s\.]/ ) {
            $feedID = $1 . " ";
        }
        $comments = "Amazon " . $feedID . $fileCountry . " Sales for " . $revenuePeriod;
    } elsif ( $serviceID == BookPub::Tracker::Service::COPIA()
        || $serviceID == BookPub::Tracker::Service::FOLLETT() ) {
        my $revenuePeriod = $custRef;
        $revenuePeriod =~ s/HE//;

        if ( $countryCode eq 'CA' || $countryCode eq 'US' ) {
            $comments = $serviceName . " " . $countryCode . " Sales for " . $revenuePeriod;
        } else {
            $comments = $serviceName . " WW Sales for " . $revenuePeriod;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION() ) {
        my $revenuePeriod = $custRef;
        $revenuePeriod =~ s/ TRADE|HE//;

        if ( $countryCode eq 'CA' || $countryCode eq 'US' ) {
            $comments = "Follett FMS " . $countryCode . " Sales for " . $revenuePeriod;
        } else {
            $comments = "Follett FMS WW Sales for " . $revenuePeriod;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BARNES_NOBLE_EDUCATION() ) {

        # another one where we're just trying to shorten the name to fit within the 40 character limit
        # ick
        my $revenuePeriod = $custRef;
        $revenuePeriod =~ s/HE//;
        $comments = "Barnes & Noble Edu " . $countryCode . " Sales for " . $revenuePeriod;
    } elsif ( $serviceID == BookPub::Tracker::Service::IN_AUDIO && $fileName =~ /Digital Royalty/i ) {
        my $rChannel = $sale->r_channel =~ /^My Audiobook Library$/i ? 'M Audiobook Library' : $sale->r_channel;
        $comments = $rChannel . " " . $countryCode . " Sales for " . $custRef;
    } elsif ( $serviceID == BookPub::Tracker::Service::OPEN_ROAD_MEDIA() ) {
        my $revenuePeriod = $custRef;
        $comments = "Open Road " .  $countryCode . " Sales for " . $revenuePeriod;
    } elsif ( $serviceID == BookPub::Tracker::Service::PLAYAWAY() ) {
        (my $revenuePeriod = $custRef) =~ s/ / - /g;
        $comments = "PLAYAWAY - $revenuePeriod"
    } else {

        # Default comments value
        my $revenuePeriod = $custRef;
        $revenuePeriod =~ s/HE//;
        $comments = $serviceName . " " . $countryCode . " Sales for " . $revenuePeriod;
    }

    # We need to make sure we didn't go over the limit for this field.
    if ( length($comments) > 40 ) {
        $error = "Comments Length";
    }

    return ( $comments, $error );
}

sub _getCustRef {
    my ( $self, $serviceID, $fileName, $saleDate, $fileID ) = @_;

    # sah - don't use the sale record's date begin unless
    # that's all you  have and you know it represents the
    # reporting date.
    #
    # like for findaway world

    my %monthNames = (
        "january"   => "01",
        "jan"       => "01",
        "february"  => "02",
        "feb"       => "02",
        "march"     => "03",
        "mar"       => "03",
        "april"     => "04",
        "apr"       => "04",
        "may"       => "05",
        "june"      => "06",
        "jun"       => "06",
        "july"      => "07",
        "jul"       => "07",
        "august"    => "08",
        "aug"       => "08",
        "september" => "09",
        "sep"       => "09",
        "sept"      => "09",
        "october"   => "10",
        "oct"       => "10",
        "november"  => "11",
        "nov"       => "11",
        "december"  => "12",
        "dec"       => "12",
    );

    # Time to parse the file name to find the revenue period.
    #
    my $custRef;
    my $error;

    if ( $serviceID == BookPub::Tracker::Service::ADAMS_BOOK_COMPANY() ) {

        # Reporting period might not be in the filename, so we'll try to figure it out from the sales dates.
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
        $custRef = $reportingYear . "-" . $reportingMonth;

        if ( $fileName =~ / (\w+)-(\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::AMAZON() ) {
        if (   $fileName =~ /(\d{4})(\d{2})01-\d{8}/
            || $fileName =~ /(\d{4})-(\d{2})-01/
            || $fileName =~ /(\d{4}).(\d{2}\.)/
            || $fileName =~ /\d{8}-(\d{4})(\d{2})(2[89]|3[01])/ ) {
            $custRef = $1 . "-" . $2;

            # And now to tack on the country code for non-US files.
            if ( $fileName =~ /^\w{5} (\w{2})[\s\.]/ ) {
                if ( $1 ne 'US' ) {
                    $custRef .= "-" . $1;
                }
            } else {
                $custRef .= "-WW";
            }

            if ( $fileName =~ /MXARQ/i ) {
                $custRef .= "HEREN";
            } elsif ( $fileName =~ /MXAQQ/ ) {
                $custRef .= "HE";
            }
        } elsif ( $fileName =~ /Amazon CA (\w+) (\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName} . "-CA";
            }
        } elsif ( $fileName =~ /BB KEP (\w+) (\w{2,4})/ ) {
            my $monthName = lc($1);
            my $year = $2 > 2000 ? $2 : "20" . $2;
            if ( $monthNames{$monthName} ) {
                $custRef = $year . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ /HOAQC_CN_DigitalEBooksPaymentReport_/ ) {
            $custRef = "CN";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::APPLE() ) {
        if ( $fileName =~ / (\d{2})(\d{2}) \w{2}/ ) {
            $custRef = "20" . $2 . "-" . $1;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BAKERANDTAYLOR() ) {
        if ( $fileName =~ /(\d{2}).(\d{2})/ ) {
            $custRef = "20" . $2 . "-" . $1;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BARNES_NOBLE() ) {

        # This is the only one that includes day
        if ( $fileName =~ /(eBook|nook) Pay (\d{2})(\d{2})(\d{4})/i ) {
            my $year  = substr( $4, 2 );
            my $month = $2;
            my $day   = $3;
            $custRef = "BN" . $year . $month . $day;
        } elsif ( $fileName =~ /(eBook|nook) Pay (\d{2})(\d{2})(\d{2})/i ) {
            my $year  = $4;
            my $month = $2;
            my $day   = $3;
            $custRef = "BN" . $year . $month . $day;
        } elsif ( $fileName =~ /ebook Int (\d{2})(\d{2})(\d{4})/i ) {
            my $year  = substr( $3, 2, 2 );
            my $month = $1;
            my $day   = $2;
            $custRef = "BN" . $year . $month . $day;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BITLIT_MEDIA() ) {
        if ( $fileName =~ /-(\d{4})-(\w+)[\.-]/ ) {
            my $monthName = lc($2);
            if ( $monthNames{$monthName} ) {
                $custRef = $1 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOKSHOUT() ) {
        if ( $fileName =~ /(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOKSOURCE() ) {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}[ _]to/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::CHEGG() ) {
        if ( $fileName =~ /(\d{4})-(\d{2})/ ) {
            $custRef = $1 . "-" . $2;

            if ( $fileName !~ /Trade/i ) {
                $custRef .= "HE";
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::COPIA() ) {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /(\d{2})\d{2}(\d{2})/ ) {
            $custRef = "20" . $2 . "-" . $1;
        } elsif ( $fileName =~ /(\d{2}).\d{2}.(\d{4})/ ) {
            $custRef = $2 . "-" . $1;
        } elsif ( $fileName =~ / - (\d{4})\.xlsx/ ) {
            $custRef = $1;
        }

        if ( $custRef && $fileName =~ /Copia[\s_](EDU|Student)/i ) {
            $custRef .= "HE";
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::DIESEL_EBOOKS() ) {
        if ( $fileName =~ /(\d{2})(\d{4})/ ) {
            $custRef = $2 . "-" . $1;
        } elsif ( $fileName =~ / (\w+).(\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::DRAGONMOUNT() ) {
        if ( $fileName =~ /(\d{2}).(\d{4})/ ) {
            $custRef = $2 . "-" . $1;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::EBOOK() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::ENTITLE_BOOKS() ) {
        if ( $fileName =~ /^(\d{4})-(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::EPIC() ) {
        if ( $fileName =~ /(\d{1,2})-\d{1,2}-(\d{4})\./ ) {
            my ( $month, $year ) = ( $1, $2 );
            $custRef = sprintf( "%04d-%02d", $year, $month );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::DE_MARQUE() ) {
        if ( $fileName =~ /(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
        elsif ( $fileName =~ /(\w+) (\d{4}).*?\.\w+$/) {
            $custRef = $2 . "-" . $monthNames{lc($1)};
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::FEEDBOOKS() ) {
        if ( $fileName =~ /(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::IN_AUDIO() ) {
        if ( $fileName =~ / (january|february|march|april|may|june|july|august|september|october|november|december) /i ) {
            my $monthName = lc($1);

            # if the filename month agrees with the sale date month, we've got a winner
            if ( $monthNames{$monthName} && $monthNames{$monthName} eq substr( $saleDate, 5, 2 ) ) {
                $custRef = substr( $saleDate, 0, 4 ) . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ /(\d{2})-(\d{4}) / ) {
            $custRef = $2 . "-" . $1;
        } elsif ( $fileName =~ /(\d{4})-(\d{2}) / ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::FOLLETT() ) {
        if ( $fileName =~ / (\w+) (\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION() ) {
        if ( $fileName =~ /(\d{4}) (\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ / (\w+) (\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GARDNERS() ) {
        if ( $fileName =~ / (\d{4}) (\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /^VL(\d{2})(\d{4})/ ) {
            $custRef = $2 . "-" . $1;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GLOSE() ) {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /Macmillan.(?:CA[ _])?(\d{4})(\d{2}).Bulk/i ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE() ) {
        if ( $fileName =~ /(\d{4})\s*(\d{2})/ ) {
            $custRef = $1 . "-" . $2;

            if ( $fileName =~ /-HE-/ ) {
                $custRef .= "HE";
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE_PLAY() ) {
        if ( $fileName =~ /(\d{4})\s*(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::HUMMINGBIRD_DIGITAL() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::INKTERA() ) {
        if ( $fileName =~ /(\d{4})-(\d{2})-\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::KNO() ) {
        if ( $fileName =~ /(\d{4}) (\d{1,2})/ ) {
            $custRef = sprintf( "%04d-%02d", $1, $2 );

            if ( $fileName =~ /CC000008/ ) {
                $custRef .= "HE";
            }
        } elsif ( $fileName =~ / (\w+).(\d{4}) / ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::KOBO() ) {
        if ( $fileName =~ /( |~|-)(\w+).(\d{4})/ ) {
            my $monthName = lc($2);
            if ( $monthNames{$monthName} ) {
                $custRef = $3 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LANGUAGE_WORLD() ) {
        if ( $fileName =~ / (\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LIBRI() ) {
        if ( $fileName =~ / (\d{4}) (\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LIBRIFY() ) {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LSI() ) {
        if ( $fileName =~ /\d{7}.(\d{4}).(\d{2})/ ) {
            $custRef = $1 . "-" . $2;

            if ( $fileName =~ /Wholesale/i ) {
                $custRef .= "W";
            }
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::MACKIN() ) {
        if ( $fileName =~ /(\d{4}) (\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::OVERDRIVE() ) {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::ODILO() ) {
        if ( $fileName =~ /(\d{4})[_\s](\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /(\d{4})[_\s](\w+)[-\.]/ ) {
            my $monthName = lc($2);
            if ( $monthNames{$monthName} ) {
                $custRef = $1 . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ /^Macmillan (\w+) \w+ \w+[_\s](\d{4})/ || $fileName =~ /^Macmillan (\w+) \w+[_\s](\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ / ([a-z]{3,9}) (\d{4}) /i ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ /^RE.(\w+?)[_ ](\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::OLF() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::PERUSALL() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::OYSTER() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::READBOOKS() ) {
        if ( $fileName =~ /(\d{4})-(\d{1,2})\./ ) {
            $custRef = sprintf( "%04d-%02d", $1, $2 );
        } elsif ( $fileName =~ / (\w+) (\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::READWELL() ) {
        if ( $fileName =~ /(\d{4})-(\d{2})-\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ / (\w{3,9}) (\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::RECORDED_BOOKS() ) {
        if ( $fileName =~ /(\d{2})-(\d{2})/ ) {
            $custRef = "20" . $2 . "-" . $1;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::SCRIBD() ) {

        # Special hack for case 12306
        if ( $fileName =~ /2015 08 extra payment/ ) {
            $custRef = "FEB-JUNE2015";
        } elsif ( $fileName =~ / (\d{4}) (\d{2}) / ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /Macmillan(\w+)(\d{2})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = "20" . $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::SKOOBE() ) {
        if ( $fileName =~ /^(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::SONY() ) {
        if ( $fileName =~ / (\w{3}).(\d{2})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = "20" . $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::THREE_M() || $serviceID == BookPub::Tracker::Service::OCLC() )    # this service is now OCLC (previously Bibliotheca)
    {
        if ( $fileName =~ /(\d{4})(\d{2})\d{2}/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /(\d{4}) (\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } else {
            my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
            $custRef = $reportingYear . "-" . $reportingMonth;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::TRAJECTORY() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::TXTR() ) {
        if ( $fileName =~ /(\d{4})(\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        } elsif ( $fileName =~ /(\d{4})[\s\-_](\d{2})/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::VIRDOCS() ) {

        # We can't use the date in the file name anymore, so we'll try to figure it out from the sales dates.
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        if ( $reportingYear && $reportingMonth ) {
            $custRef = $reportingYear . "-" . $reportingMonth;
        } else {
            if ($fileName =~ /^MacMillan.Trade.-.(\w+?).(\d{4})/i){
                $custRef = $2 . "-" . $monthNames{$1};
            }

            # If the files span a quarter, we'll have to look for a month in the file name.
            foreach my $monthName ( keys %monthNames ) {
                if ( $fileName =~ /$monthName/i ) {
                    $reportingMonth = $monthNames{$monthName};
                    $custRef        = $reportingYear . "-" . $reportingMonth unless $custRef;
                    last();
                }
            }

            # adding another way to find the date in the file name
            if ( !$reportingYear ) {
                if ( $fileName =~ /consolidated (\d{4}) (\d{2})/i ) {
                    $reportingYear  = $1;
                    $reportingMonth = $2;
                    $custRef        = $reportingYear . "-" . $reportingMonth;
                }
            }
        }

        # The file name must contain eithe 'trade' or 'higher_education'.  Otherwise, we throw an error.
        if ( $fileName =~ /higher.education/i ) {
            $custRef .= " HE";
        } elsif ( $fileName !~ /trade/i ) {
            my $serviceName = $serviceNames{$serviceID};
            $error = "Unable to find 'trade' or 'higher_education' in file name '" . $fileName . "' for " . $serviceName;
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::VITAL_SOURCE()
        || $serviceID == BookPub::Tracker::Service::BARNES_NOBLE_EDUCATION )    # distributed by Vital Source
    {
        if ( $fileName =~ /\.(\w{3,9}).(\d{2})\./ ) {
            if ( exists $monthNames{ lc $1 } ) {
                $custRef = "20$2" . "-" . $monthNames{ lc $1 };
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::YUZU() ) {
        if ( $fileName =~ /.(\w+).(\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::ZOLA_BOOKS() ) {
        if ( $fileName =~ /(\w+).(\d{4})/ ) {
            my $monthName = lc($1);
            if ( $monthNames{$monthName} ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        } elsif ( $fileName =~ /^(\d{4}).(\d{2})/ ) {
            {
                $custRef = $1 . "-" . $2;
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::PERMA_BOUND() ) {
        if ( $fileName =~ /.(\d{4}).(0[1-9]|1[0-2])\..+$/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOKPAL() ) {
        if ( $fileName =~ /[_ ](\d{4})[_ ](\d{1,2}).*[.].+$/ ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::HOOPLA_DIGITAL() ) {
        if ( $fileName =~ /\d+\-(\d{4})\-\d{1,2}\-\d{1,2}.*[\s_]([a-zA-z]+).+$/ ) {
            my $monthName = lc($2);
            $custRef = $1 . "-" . $monthNames{$monthName} if exists $monthNames{$monthName};
        } else {
            my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
            $custRef = $reportingYear . "-" . $reportingMonth if $reportingYear && $reportingMonth;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::CAPELLA() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
        $custRef = $reportingYear . "-" . $reportingMonth if $reportingYear && $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::DOWNPOUR() ) {
        if ( $fileName =~ /([a-z]{3,9})[ _-](\d{4}).+$/i ) {
            my $monthName = lc($1);
            $custRef = $2 . "-" . $monthNames{$monthName} if exists $monthNames{$monthName};
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LIBRO() ) {
        if ( $fileName =~ /([a-z]{3,9})[ _-](\d{4}).+$/i ) {
            my $monthName = lc($1);
            $custRef = $2 . "-" . $monthNames{$monthName} if exists $monthNames{$monthName};
        }
        elsif ( $fileName =~ /^(\d{4})-(\d{2})/i ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::AUDIOBOOKSNOW() ) {
        if ( $fileName =~ /([a-z]{3,9})[ _-](\d{4}).+$/i ) {
            my $monthName = lc($1);
            $custRef = $2 . "-" . $monthNames{$monthName} if exists $monthNames{$monthName};
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::SIMPLY_AUDIO() ) {
        if ( $fileName =~ /^(\d{4})[. _-](\d{1})th/i ) {
            my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);
            $custRef = $reportingYear . "-" . $reportingMonth;
        } elsif ( $fileName =~ /^(\d{2})[. _-](\d{4})/i ) {
            $custRef = "$2-$1";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::AUDIOBOOK_STORE() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::ONEWORLD_CONTENT() ) {
        if ( $fileName =~ /(\w+)[ -](\d{4})\./i ) {
            my $monthName = lc($1);
            $custRef = $2 . "-" . $monthNames{$monthName} if exists $monthNames{$monthName};
        }
    }
    elsif ( $serviceID == BookPub::Tracker::Service::RSFORMAT() ) {
        if ( $fileName =~ m/Perusall[ _-](Trade|Learning)[ _-]IA[ _-]Sales[ _-](\d{4})[ _-](\d{2})/i ) {
            $custRef = "$2-$3";
            $custRef .= "HE" if $1 =~ /learning/i;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::RALLY_READER() ) {
        if ( $fileName =~ m/.*RALLYREADER[_ ](\d{2})(\d{4}).+/i ) {
            $custRef = "$2-$1";
        } elsif ( $fileName =~ /^RALLYREADER[_ ](?:R[_ ])?\w{2}[_ ](\d{2})(\d{4})[_ ]MACMILLIAN/i ) {
            $custRef = "$2-$1";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::IZNEO() ) {
        if ( $fileName =~ m/.+?(\d\d)(\d\d)[ _]MACMILLAN.*/i ) {
            $custRef = sprintf "20%02d-%02d", $1, $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::FABLE() ) {
        if ( $fileName =~ m/Macmillan.*(\d{4})-(\d{1,2})-(\d{1,2}).*/i ) {
            $custRef = sprintf "%02d-%02d", $1, $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BLINKIST() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::CHIRP() ) {
        if ( $fileName =~ /Macmillan.+? (\w+?) (\d{4})\./ ) {
            my $monthName = lc($1);
            if ( exists($monthNames{$monthName}) ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOK_OF_THE_MONTH() ) {
        if ( $fileName =~ /Macmillan.(\w+?).(\d{4}).Audiobook/ ) {
            my $monthName = lc($1);
            if ( exists($monthNames{$monthName}) ){
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::SPEECHIFY() ) {
        if ( $fileName =~ /macmillan.(?:Audiobook.|ebook.)?(\w+?).(\d{4})/i ) {
            my $monthName = lc($1);
            if ( exists($monthNames{$monthName}) ) {
                $custRef = $2 . "-" . $monthNames{$monthName};
            }
        }
        if ( $fileName =~ /macmillan.(?:Audiobook.|ebook.)?(\d{4}).(\d{2})/i ) {
            $custRef = $1 . "-" . $2;
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::YOTO() ) {
        if ( $fileName =~ /Statement (\w+) (\d{2,4})(?: F)\./i ) {
            my $month = lc(substr($1, 0, 3));
            my $year = $2;
            $year = "20$year" if length($year) == 2;
            $custRef = $year . "-" . $monthNames{$month} if exists $monthNames{$month};
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOKSIO() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::AUDIBLE() ) {
        if ( $fileName =~ /Audible.MONTHLY.(\w{3,9}).(\d{4})\./i
        || $fileName =~ /Macmillan.Audio.US.MONTHLY.(\w{3,9}).(\d{4})\./i
        || $fileName =~ /MONTHLY.(\w{3,9}).(\d{4})\./i
        ) {
            my $MMM = uc(substr($1, 0, 3));
            my $YY = substr($2, -2);
            $custRef = $MMM . $YY;
        } elsif ( $fileName =~ /Q.(\d).(\d{4})/i ) {
            my $quarter = $1;
            my $year = $2;
            $custRef = "Q$quarter $year";
        }
    } elsif (  $serviceID == BookPub::Tracker::Service::SPOTIFY() ) {
        if ( $fileName =~ / (\w{3,9}) (\d{4}) /i ) {
            my $month = lc(substr($1, 0, 3));
            my $year = $2;
            $custRef = $year . "-" . $monthNames{$month} if exists $monthNames{$month};
        } elsif ( $fileName =~ /\b(\d{4})(\d{2})\b/ ) {
            my $year = $1;
            my $month = $2;
            $custRef = $year . "-" . $month if $month;
        }
    } elsif (  $serviceID == BookPub::Tracker::Service::LEGIBLE() ) {
        if ( $fileName =~ /^(\d{4}).(\d{2})/ ) {
            $custRef = substr($fileName, 0, 7);
        }
    } elsif (  $serviceID == BookPub::Tracker::Service::BOOKSHOP() ) {
        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        $custRef = $reportingYear . "-" . $reportingMonth;
    } elsif ( $serviceID == BookPub::Tracker::Service::OPEN_ROAD_MEDIA() ) {
        if ( $fileName =~ /(\w{3,9}).(\d{4})/i ) {

            my $month = uc(substr($1, 0, 3));
            my $year = length($2) == 2 ? "20$2" : $2;
            $custRef = "$month" . "$year";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::PLAYAWAY ) {
        if ( $fileName =~ /(\d{4})\s+Q(\d)/i ) {
            my $year = $1;
            my $quarter = $2;
            $custRef = "Q$quarter $year";
        }
    }

    if ($custRef) {
        # Let's do a sanity check on the date
        my $year;
        my $month;
        my $day;
        my $quarter;
        my $badYear    = 0;
        my $badMonth   = 0;
        my $badDay     = 0;
        my $badQuarter = 0;

        my $currentYear = (localtime)[5] + 1900;

        if ( $custRef =~ /BN(\d{2})(\d{2})(\d{2})/ ) {
            $year  = "20" . $1;
            $month = $2;
            $day   = $3;
        } elsif ( $custRef =~ /(\d{4})-(\d{2})/ ) {
            $year  = $1;
            $month = $2;
        } elsif ( $custRef =~ /^(\d{4})\D*$/ || $custRef =~ /^FEB-JUNE(\d{4})$/ ) {
            $year  = $1;
            $month = "YEAR_ONLY";
        } elsif ( $custRef =~ /^([A-Z]{3})(\d{2,4})$/ ) {
            $year  = length($2) == 2 ? "20$2" : $2;
            $month = $monthNames{lc $1};
        } elsif ( $custRef =~ /^Q(\d) (\d{4})$/i ) {
            $year = $2;
            $quarter = $1;
        }

        if ( $year > $currentYear || $year < 2006 ) {
            $badYear = 1;
        } elsif ( $month && ( $month > 12 || $month < 1 ) && $month ne 'YEAR_ONLY' ) {
            $badMonth = 1;
        } elsif ( $day && ( $day > 31 || $day < 1 ) ) {
            $badDay = 1;
        } elsif ( $quarter && $quarter !~ /^(?:1|2|3|4)$/ ) {
            $badQuarter = 1;
        }

        if ( $badYear || $badMonth || $badDay || $badQuarter ) {
            my $serviceName = $serviceNames{$serviceID};

            my $_period = $quarter ? "Q${quarter} ${year}" : "${year}-${month}";
            $error = "Invalid date '$_period' from filename '$fileName' for $serviceName";
        }
    } else {
        my $serviceName = $serviceNames{$serviceID};

        $error = "Unable to parse file name '" . $fileName . "' for " . $serviceName;
    }

    return ( $custRef, $error );
}

sub _getClientService {
    my ( $self, $sale, $countryCode, $fileName ) = @_;

    # We want to use the normalize version of country code (which is really just the US territories set to US)
    # so it has to be passed in separately, rather than retrieved from the sale record like the rest of these.
    my $serviceID    = $sale->service_id;
    my $productType  = $sale->product_type;
    my $currencyCode = $sale->currency_code;
    my $institution  = $sale->r_institution;
    my $channel      = $sale->r_channel;

    my $clientService;

    # We need to send a back the list of fields and their values that we used to look for the SAN for a service.
    my %sanCriteria;

    # Some services have multiple client service IDs, depending on the country code.
    # So, we'll have to look them up a little differently.
    if (   $serviceID == BookPub::Tracker::Service::BARNES_NOBLE()
        || $serviceID == BookPub::Tracker::Service::COPIA()
        || $serviceID == BookPub::Tracker::Service::DRAGONMOUNT()
        || $serviceID == BookPub::Tracker::Service::EBOOK()
        || $serviceID == BookPub::Tracker::Service::ENTITLE_BOOKS()
        || $serviceID == BookPub::Tracker::Service::EPIC()
        || $serviceID == BookPub::Tracker::Service::FOLLETT()
        || $serviceID == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION()
        || $serviceID == BookPub::Tracker::Service::GOOGLE_PLAY()
        || $serviceID == BookPub::Tracker::Service::LIBRIFY()
        || $serviceID == BookPub::Tracker::Service::OYSTER()
        || $serviceID == BookPub::Tracker::Service::READBOOKS()
        || $serviceID == BookPub::Tracker::Service::READWELL()
        || $serviceID == BookPub::Tracker::Service::RECORDED_BOOKS()
        || $serviceID == BookPub::Tracker::Service::SCRIBD()
        || $serviceID == BookPub::Tracker::Service::TXTR()
        || $serviceID == BookPub::Tracker::Service::CAPELLA() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $serviceID == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION() && $countryCode eq 'US' && $sale->r_comments eq "MAC" ) {
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => "MAC-US", product_type => $productType );
        } else {

            # First, look for one specific to the country code
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );

            # If we didn't find a match, look for a default one with no country code
            if ( !$clientService ) {
                $clientService =
                  BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ANY', product_type => $productType );
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GLOSE() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Currency Code'} = $currencyCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $fileName =~ /Macmillan[ _]\d{6}[ _]Bulk\..*$/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'ANY',
                name         => 'Glose Bulk',
                product_type => $productType
            );
        } elsif ( $fileName =~ /macmillan-audio-us-\d{8}-\d{8}\..*$/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'US_AUDIO',
                name         => 'Glose Non Bulk Audio US',
                product_type => $productType
            );
        } elsif ( $fileName =~ /Macmillan-CA[ _]\d{6}[ _]Bulk\..*$/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'BULK_CA',
                name         => 'Glose Bulk CA',
                product_type => $productType
            );
        } elsif ( $fileName =~ /Macmillan[ _]\d{6}[ _]Bulk[ _]Audio\..*$/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'ANY',
                name         => 'Glose Bulk Audio',
                product_type => $productType
            );
        } elsif ( $fileName =~ /Bulk/i ) {
            # Other files with the Bulk part in the file name
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'ANY',
                name         => 'Glose Bulk',
                product_type => $productType
            );
        }
        else {
            # Other files
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id    => $serviceID,
                feed_id       => $countryCode,
                currency_code => $currencyCode,
                product_type  => $productType
            );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::KOBO() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $fileName =~ /WM[_ ]MACMILLAN[_ ]-[_ ]WM/i || $channel =~ /walmart/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                product_type => $productType,
                name         => 'Kobo AUDB Walmart'
              )
              || BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                product_type => $productType,
                name         => 'Kobo EBOK Walmart'
              );
        } elsif ( $fileName =~ /^Sub[_ ]MACMILLAN[_ ~]MACMILLAN/i ) {
            my $feedID = 'ANY' if $countryCode !~ /^(US|CA)$/i;
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                product_type => $productType,
                feed_id      => $feedID,
                name         => 'Kobo EBOK Subscription'
            );
        } else {
            $clientService =
                 BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $countryCode )
              || BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => 'ANY' );
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::ADAMS_BOOK_COMPANY() ) {
        $sanCriteria{'Country Code'} = $countryCode;

        # First, look for one specific to the country code
        $clientService =
          BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );

        # If we didn't find a match, look for a default one with no country code
        if ( !$clientService ) {
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ANY', product_type => $productType );
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::AMAZON() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Currency Code'} = $currencyCode;
        $sanCriteria{'Product Type'} = $productType;

        # This is an Amazon CA file, but we want to use a special SAN for it.
        # So, here's an ugly hack to do that.
        my $feedID;
        if ( $fileName =~ /^HOLQQ CA CAD/i ) {
            $countryCode = "HOLQQ CA CAD";
        }  elsif ( $fileName =~ /^MMHQQ/i && $currencyCode eq "CAD" ) {
            $countryCode = "MMHQQ CA CAD";
        } elsif ( $fileName =~ /^MMTQQ/i && $sale->r_model =~ /Lending/i ) {
            $feedID = "MMTQQ $countryCode";
        } elsif ( $fileName =~ /^MMTQQ/i && $sale->r_model =~ /Prime Reading/i ) {
            $feedID = "MMTQQ $countryCode PR";
        } elsif ( $fileName =~ /^kep.print/i ) {
            $feedID = "KEP " . ($countryCode !~ /^(CA|US)$/ ? "ROW" : $countryCode);
        } elsif ( $fileName =~ /^kep /i ) {
            $feedID = "KEP " . $currencyCode;
        } elsif ( $fileName =~ /^bb kep /i ) {
            $feedID = "BB KEP " . $currencyCode;
        } elsif ( $fileName =~ /^Amazon CA/i ) {    # RSD-8761
            $feedID = 'CA';
        }

        if ( $feedID ) {
            # First, look for one specific to the feed_id
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => $feedID,
                product_type => $productType
            );
        } elsif ( $sale->r_model !~ /Prime Reading/i ) {
            # Second, look for one specific to the country_code
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => $countryCode,
                product_type => $productType
            );
        }

        # Last, if we didn't find a match, look for a default one with no country_code/feed_id
        if ( !$clientService && $sale->r_model !~ /Prime Reading/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => 'ANY',
                product_type => $productType
            );
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::APPLE() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        my $feed;
        if ( $fileName =~ /^85004137/ ) {
            $feed = ($countryCode =~ /^(US|CA)$/) ? $countryCode : "ANY";
            $feed = "ANY" if $fileName =~ /BR[ _]WHT/i;
            $clientService = BookPub::DB::Item::ClientService->Lookup(service_id => $serviceID, feed_id => $feed, product_type => $productType);
        }
        elsif ( $fileName =~ /^85161879/ ) {
            $feed = ($fileName =~ /BR.WHT/i) ? "BR" : "85161879";
            $clientService = BookPub::DB::Item::ClientService->Lookup(service_id => $serviceID, feed_id => $feed, product_type => $productType);
        }
        elsif ( $productType eq "AUDB" ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(service_id => $serviceID, feed_id => $countryCode, product_type => $productType);
        }

        if ( !$clientService ) {
            # Everything else gets lumped into the global bucket.
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ANY', product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BAKERANDTAYLOR() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;
        $sanCriteria{'Comments'}     = $sale->r_comments;

        if ( $fileName =~ /Agency/i ) {
            # If "Agency" is in the file name, we can look this up normally using the country code for the feed id.
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => $countryCode,
                product_type => $productType
            );
        } elsif ( $fileName =~ /ARPA/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => "ARPA-LIBRARY-US",
                product_type => $productType
            );
        } elsif ( $countryCode eq 'US' ) {
             if ( $sale->r_comments =~ /^Axis-(Library|Academic)$/i ) {
                my $feedID = uc($1);
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $feedID,
                    product_type => $productType
                );
            } elsif ( $sale->r_comments =~ /^Axis-(?:School|Education)$/i ) {
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => "SCHOOL",
                    product_type => $productType
                );
            }
        } elsif ( $countryCode eq 'CA' ) {
            if ( $sale->r_comments =~ /^Axis-(Library|Academic) Intl$/i ) {
                my $feedID = uc($1) . "-CA";
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $feedID,
                    product_type => $productType
                );
            } elsif ( $sale->r_comments =~ /^Axis-(?:School|Education)$/i ) {
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => "SCHOOL-CA",
                    product_type => $productType
                );
            }
        } elsif ( $countryCode !~ /^(?:US|CA)$/ ) {
            if ( $sale->r_comments =~ /^Axis-(Library|Academic) Intl$/i ) {
                my $feedID = uc($1) . "-ROW";
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $feedID,
                    product_type => $productType
                );
            } elsif ( $sale->r_comments =~ /^Axis-(?:School|Education)$/i ) {
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => "SCHOOL-ROW",
                    product_type => $productType
                );
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::DE_MARQUE() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        $countryCode = "ROW" if $sale->country_code !~ /^(?:US|CA)$/i;
        my $feedID = "";

        if ( $fileName =~ /^RoyaltyShare DigitalSalesTemplate DeMarque \w{3,9} \d{4} [a-z]+ ([a-z]+).*$/i ) {
            my $fileType = lc($1);

            if ($fileType =~ /^(library|trade)$/i ) {
                $feedID = uc($fileType) . "-" . $countryCode;
            }
        }
        elsif ($sale->r_comments =~ /^(library|consumer)$/i) {
            $feedID = "LIBRARY-$countryCode" if $sale->r_comments =~ /library/i;
            $feedID = "TRADE-$countryCode" if $sale->r_comments =~ /consumer/i;
        }
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feedID, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::FEEDBOOKS() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $fileName =~ /lib sales/i ) {
            my $constructed_feed_id = 'LIBRARY-';

            if ( $countryCode eq 'US' || $countryCode eq 'CA' ) {

                # First, look for one specific to the country code
                $constructed_feed_id .= $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $constructed_feed_id,
                    product_type => $productType
                );
            } else {

                # then ROW
                $constructed_feed_id .= 'ANY';
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $constructed_feed_id,
                    product_type => $productType
                );
            }
        } else {

            # for the non-library sales...
            # First, look for one specific to the country code
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );

            # If we didn't find a match, look for a default one with no country code
            if ( !$clientService ) {
                $clientService =
                  BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ANY', product_type => $productType );
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GARDNERS() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        # All Audiobook sales fall under the same SAN for all territories (RSD-5598)
        if ($productType eq "AUDB") {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType );
        }
        # Check if this is the Library sales file
        elsif ( $fileName =~ /EBMACM6E/i ) {
            $sanCriteria{'Feed ID'} = "EBMACM6E (LIBRARY)";
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'LIBRARY', product_type => $productType );
        } elsif ( $fileName =~ /^VL/i ) {
            $sanCriteria{'Feed ID'} = "VL (K12)";
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'K12', product_type => $productType );
        } elsif ( $fileName =~ /EMMMUS0E/i ) {
            if ( $institution =~ /ASKEWS & HOLTS LIBRARY SERVIC/i ) {    # yes, the E in service is truncated
                $sanCriteria{'Feed ID'} = "EMMMUS0E LIBRARY";
                $clientService =
                  BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'LIBRARY', product_type => $productType );
            } elsif ($channel) {                                         # any data in this column
                $sanCriteria{'Feed ID'} = "EMMMUS0E K12";
                $clientService =
                  BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'K12', product_type => $productType );
            } else {
                if ( $countryCode eq 'US' || $countryCode eq 'CA' ) {
                    $sanCriteria{'Feed ID'} = "EMMMUS0E US";
                    $clientService =
                      BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'US', product_type => $productType );
                } else {
                    $sanCriteria{'Feed ID'} = "EMMMUS0E ROW";
                    $clientService =
                      BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ROW', product_type => $productType );
                }
            }
        } else {
            my $countryCode = $sale->country_code;
            if ( $countryCode ne 'US' && $countryCode ne 'CA' ) {
                $countryCode = 'ROW';
            }

            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE() ) {

        $sanCriteria{'Product Type'} = $productType;

        # First, check if this is the K-12 file
        if ( $fileName =~ /K-12/i ) {
            $sanCriteria{'Currency Code'} = $currencyCode;
            $sanCriteria{'Feed ID'}       = "K-12";
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'K-12', currency_code => $currencyCode );
        } elsif ( $fileName =~ /-HE-/i ) {
            $sanCriteria{'Currency Code'} = $currencyCode;
            $sanCriteria{'Feed ID'} = "HE";
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'HE', currency_code => $currencyCode );
        } else {
            $sanCriteria{'Country Code'} = $countryCode;

            # Otherwise look one specific to the country code
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );

            # If we didn't find a match, look for a default one with no country code
            if ( !$clientService ) {
                $clientService =
                  BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'ANY', product_type => $productType );
            }
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::HUMMINGBIRD_DIGITAL() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;
        # Audiobook has only SAN
        $countryCode = 'ANY' if $productType =~ /^AUDB$/i;
        # eBook has two SANs
        $countryCode = 'ANY' if $countryCode !~ /^US$/i;
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::BOOKSIO ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        my $feedID;

        if ($countryCode =~ /^CA$/i && $productType eq 'EBOK') {
            $feedID = 'CA';
        }
        elsif ($productType =~ /^AUDB$/i || $countryCode !~ /^US$/i) {
            $feedID = 'ANY';
        }
        else {
            $feedID = $countryCode;
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feedID, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::INKTERA() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $countryCode ne 'US' && $countryCode ne 'CA' ) {
            $countryCode = 'ROW';
        }

        $clientService =
          BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::LANGUAGE_WORLD() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $countryCode ne 'US' && $countryCode ne 'CA' ) {
            $countryCode = 'ROW';
        }

        $clientService =
          BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::LSI() ) {
        my $countryCode = $sale->country_code;
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;
        $sanCriteria{'Is Agency'}    = $sale->is_agency;

        my $feedID = 'ANY';
        my $isAgency = $sale->is_agency;

        if ( $productType =~ /^PBOK$/i && $isAgency eq 'N' ) {
            ($feedID) = $fileName =~ /^sales[ _]comp[ _]LSI[ _]((?:GLOBAL|POD[ _]Wholesale)[ _][A-Z]{3}[ _]\d{7})[ _]\d{4}[ _]\d{2}/i;
            $feedID =~ s/\s+/_/g;
            $feedID =~ s/GLOBAL_/GL_/gi;
            $feedID =~ s/POD_Wholesale_/PW_/gi;
        } elsif ( $productType !~ /^PBOK$/i && $countryCode !~ /^(?:US|CA)$/ && $isAgency eq 'N' ) {
            $feedID = 'ROW-RRP';
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id => $serviceID,
            product_type => $productType,
            feed_id => uc($feedID)
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::ODILO() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $countryCode ne 'US' && $countryCode ne 'CA' ) {
            $countryCode = 'ROW';
        }
        my $feed_id = $countryCode;
        $feed_id = "K12-" . $countryCode if $fileName =~ /academic/i;
        $feed_id = "PPU" if $fileName =~ /PPU/; # PPU is a special feed for Odilo (capital letters)

        $clientService =
          BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::OVERDRIVE() ) {
        my $countryCode = $sale->country_code;
        # All US territories are treated as US
        $countryCode = 'US' if grep {/$countryCode/} ('AS','FM','GU','MH','MP','PR','PW','VI');

        if ( $countryCode ne 'US' && $countryCode ne 'CA' ) {
            $countryCode = 'ROW';
        }

        $sanCriteria{'Country Code'}            = $countryCode;
        $sanCriteria{'Product Type'}            = $productType;
        $sanCriteria{'Price Type Qualifier ID'} = $sale->price_type_qualifier_id;

        if ( $sale->price_type_qualifier_id == BookPub::DB::Item::PriceTypeQualifier::kCorporate ||
             $sale->price_type_qualifier_id == BookPub::DB::Item::PriceTypeQualifier::kSchoolLibrary ) {
            $sanCriteria{'Comments'} = $sale->r_comments;

            # Library sales
            if ( $fileName =~ /\d{4}\d{2}\d{2}[- ]ARPA/i ) {
                my $feedID = "LIBRARY-ARPA";
                $clientService =  BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $feedID,
                    product_type => $productType
                );
            }
            elsif ( lc( $sale->r_comments ) eq 'education' ) {
                my $feedID = "K12-" . $countryCode;
                $clientService =  BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $feedID,
                    product_type => $productType
                );
            }
            elsif ( lc( $sale->r_comments ) eq 'institutional' ) {
                my $feedID = "LIBRARY-" . $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                   service_id   => $serviceID,
                   feed_id      => $feedID,
                   product_type => $productType
                );
            }
            elsif ( lc( $sale->r_comments ) eq 'education - class set' ) {
                $countryCode = 'US' if $sale->country_code eq 'MP';
                my $feedID = "EDCL_SET-" . $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                   service_id   => $serviceID,
                   feed_id      => $feedID,
                   product_type => $productType
                );
            }
            elsif ( lc( $sale->r_comments ) eq 'institutional - cpc' ) {
                my $feedID = "INST_CPC-" . $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                   service_id   => $serviceID,
                   feed_id      => $feedID,
                   product_type => $productType
                );
            }
            elsif ( lc( $sale->r_comments ) eq 'education - cpc' ) {
                my $feedID = "EDU_CPC-" . $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                   service_id   => $serviceID,
                   feed_id      => $feedID,
                   product_type => $productType
                );
            }
        } else {
            my $feedID = $countryCode;

            # Otherwise, it must be a regular sale so look up the non-library SAN.
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feedID, product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::THREE_M() || $serviceID == BookPub::Tracker::Service::OCLC() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        # use the country for feed_id if US or CA
        if ( $countryCode eq 'US' || $countryCode eq 'CA' ) {
            if ( $countryCode =~ /^US$/i && $sale->r_model =~ /^PPU$/i ) {
                $countryCode .= '-PPU';
            }
            $clientService = BookPub::DB::Item::ClientService->Lookup(
                service_id   => $serviceID,
                feed_id      => $countryCode,
                product_type => $productType
            );
        }
        # otherwise use ROW
        else {
            $countryCode = 'ROW';
            $sanCriteria{'Country Code'} = $countryCode;
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::TRAJECTORY() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        # use the country for feed_id if US or CA
        if ( $countryCode eq 'US' || $countryCode eq 'CA' ) {
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
        }

        # otherwise use ROW
        else {
            $countryCode = 'ROW';
            $sanCriteria{'Country Code'} = $countryCode;
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => "ROW", product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::BARNES_NOBLE_EDUCATION() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;

        if ( $fileName =~ /trade/i ) {
            my $constructed_feed_id = 'TRADE-';

            if ( $countryCode eq 'US' ) {
                $constructed_feed_id .= $countryCode;
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    feed_id      => $constructed_feed_id,
                    product_type => $productType
                );
            }
        } else {
            $clientService =
              BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode, product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::HOOPLA_DIGITAL() ) {
        $sanCriteria{'Country Code'} = $countryCode;
        $sanCriteria{'Product Type'} = $productType;
        $countryCode = 'ANY' if $countryCode !~ /^(?:US|CA)$/i;

        my $feedID = $countryCode;
        if ($productType eq "EBOK") {
            $feedID = "FLEX-$countryCode"    if $sale->r_model =~ /^Flex$/i;
            $feedID = "INSTANT-$countryCode" if $sale->r_model =~ /^Instant$/i;
        }
        if ($productType eq "AUDB") {
            $feedID = "FLEX-$countryCode" if $sale->r_model =~ /^Flex$/i;
            $feedID = "FLEX-ROW" if ($sale->r_model =~ /^Flex$/i && $countryCode !~ /^(?:US|CA)$/i);
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup(
           service_id   => $serviceID,
           feed_id      => $feedID,
           product_type => $productType
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::PERUSALL ) {
        my $feedID;

        $feedID = 'Learning'    if ($fileName =~ /Learning/i && $countryCode eq 'US');
        $feedID = 'Learning CA' if ($fileName =~ /Learning/i && $countryCode eq 'CA');
        $feedID = 'Trade'       if ($fileName =~ /Trade/i && $countryCode eq 'US');
        $feedID = 'Trade CA'    if ($fileName =~ /Trade/i && $countryCode eq 'CA');
        $feedID = 'Trade ROW'   if ($fileName =~ /Trade/i && $countryCode !~ /^(?:US|CA)$/);
        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            feed_id       => $feedID,
            product_type  => $productType
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::DOWNPOUR() ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::AUDIOBOOK_STORE ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feedID = $countryCode;
        $feedID = "ROW" if $feedID !~ /^(?:US|CA)$/i;

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feedID );
    } elsif ( $serviceID == BookPub::Tracker::Service::CHEGG )  {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        if ($fileName =~ /subscription/i) {
            if ($countryCode eq 'US') {
                $clientService = BookPub::DB::Item::ClientService->Lookup(
                    service_id   => $serviceID,
                    product_type => $productType,
                    feed_id      => 'US Subscription' );
            }
        }
        else {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::RALLY_READER ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;
        if ( $fileName =~ /^RALLYREADER[_ ](?:R[_ ])?(\w{2})[_ ]\d+[_ ]MACMILLIAN/i ) {
            my $feedID = $1;
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feedID );
        } else {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $countryCode );
        }

    } elsif ( $serviceID == BookPub::Tracker::Service::IZNEO ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feed_id = $countryCode;
        $feed_id = "ROW" if ( $countryCode !~ /^CA|US$/);

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::FABLE ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feed_id = $countryCode;
        $feed_id = "ROW" if ( $countryCode !~ /^CA|US$/);

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::BLINKIST ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feed_id = $countryCode;

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType );
    } elsif ( $serviceID == BookPub::Tracker::Service::MACKIN && $productType eq 'EBOK' ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my ($feed_id, $type);
        $type = $sale->r_purchase_type;

        $feed_id = "C_US" if ($type eq "C" && $countryCode eq "US");
        $feed_id = "C_CA" if ($type eq "C" && $countryCode eq "CA");
        $feed_id = "S_CA" if ($type eq "S" && $countryCode eq "CA");
        $feed_id = "S_US" if ($type eq "S" && $countryCode eq "US");

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::SIMPLY_AUDIO ) { # Audiobooks.com
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feed_id = $countryCode;
        $feed_id = "ROW" if $feed_id !~ /^(?:CA|US)$/;

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::CHIRP ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => "ANY" );
    } elsif ( $serviceID == BookPub::Tracker::Service::SPEECHIFY ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feed_id = $countryCode =~ /^(US|CA|)$/i ? $countryCode : 'ROW';

        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::IN_AUDIO ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        # RSD-11835
        my $serviceName = 'InAudio';
        (my $partner = $sale->r_channel) =~ s/^\s+|\s+$//g;
        unless ( $partner =~ /^(?:Barnes & Noble|Books-A-Million|Chirp|Follett|OCLC)$/i ) {
            $partner = 'OTHER';
        }
        my $saleType = lc( $sale->r_purchase_type );

        my $name = "$serviceName $partner $saleType";
        my $feedID = $countryCode =~ /^(?:US|CA)$/i ? $countryCode : 'ANY';

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            product_type  => $productType,
            feed_id       => $feedID,
            name          => $name,
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::VIRDOCS ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $channel = $sale->r_channel;

        my $feedID = $countryCode;
        $feedID .= ' HE' if $channel =~ /^highereducation$/i;
        $feedID .= ' IA' if $channel =~ /^inclusive access$/i;
        $feedID .= ' EA' if $channel =~ /^equitable( access)?$/i;
        $feedID .= ' INCLUSIVE' if $channel =~ /^inclusive$/i;
        $feedID .= ' ECOMMERCE' if $channel =~ /^eCommerce$/i;

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id   => $serviceID,
            product_type => $productType,
            feed_id      => $feedID
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::VITAL_SOURCE ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $model = $sale->r_model;
        my $transaction_category = $sale->r_transaction_category;

        my $feedID = $countryCode;
        if ($countryCode ne "CA") {
            $feedID = 'US INCLUSIVE' if $model =~ /inclusive/i;
            $feedID = 'BNED' if $transaction_category =~ /^BNED/i;
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            product_type  => $productType,
            feed_id       => $feedID
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::AUDIBLE ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;


        my $purchaseType = $sale->r_purchase_type;
        my $feedID = uc($purchaseType);

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            product_type  => $productType,
            feed_id       => $feedID
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::SPOTIFY ) {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my $feedID = $countryCode =~ /^(?:us|ca)$/i ? uc($countryCode) : 'ANY';

        if ($sale->r_comments eq 'spotify-direct-sales') {
            $feedID .= " Direct";
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            product_type  => $productType,
            feed_id       => $feedID
        );
    } elsif ( $serviceID == BookPub::Tracker::Service::LIBRO ) {
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        my %params = (
            service_id   => $serviceID,
            product_type => $productType,
            feed_id      => ''
        );

        if ( $currencyCode =~ /^CAD$/i  ) {
            $params{feed_id} = 'CA';
            $params{currency_code} = $currencyCode;
        } elsif ( $currencyCode =~ /^USD$/i ) {
            $params{feed_id} ='US/ROW';
            $params{currency_code} = $currencyCode;
        }

        $clientService = BookPub::DB::Item::ClientService->Lookup(%params);
    } else {
        $sanCriteria{'Country Code'}  = $countryCode;
        $sanCriteria{'Product Type'}  = $productType;
        $sanCriteria{'Currency Code'} = $currencyCode;

        # First, try to find a client service match using the service id, product type, and currency code.
        $clientService = BookPub::DB::Item::ClientService->Lookup(
            service_id    => $serviceID,
            currency_code => $currencyCode,
            product_type  => $productType
        );

        # If that didn't work, try just using the service id and product type.
        if ( !$clientService ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, product_type => $productType );
        }
    }

    return ( $clientService, %sanCriteria );
}

sub _normalizeFileName {
    my ( $self, $fileName ) = @_;
    $fileName =~ s/_/ /g;

    # We've got a couple of feeds that are missing the country code from the name.
    # We'll add that into the name here.
    if ( $fileName =~ /^MMHQQ\./ ) {
        $fileName =~ s/MMHQQ/MMHQQ CA/;
    } elsif ( $fileName =~ /^MMMQQ\./ ) {
        $fileName =~ s/MMMQQ/MMMQQ US/;
    } elsif ( $fileName =~ /^MCMQQ\./ ) {
        $fileName =~ s/MCMQQ/MCMQQ US/;
    } elsif ( $fileName =~ /^MXAQQ\./ ) {
        $fileName =~ s/MXAQQ/MXAQQ US/;
    } elsif ( $fileName =~ /^MXARQ\./ ) {
        $fileName =~ s/MXARQ/MXARQ US/;
    }

    return $fileName;
}

sub validateFile {
    my ( $self, $fileID ) = @_;

    # This is the hash we'll send back all of the data in.
    # We'll need to know exactly which fields are missing.
    my %validation;

    # If everything is good, this is all we'll send back.
    $validation{isValid} = 1;

    my $file      = BookPub::DB::Item::File->Lookup( file_id => $fileID );
    my $serviceID = $file->service_id;
    my $fileName  = $self->_normalizeFileName( $file->orig_file_name );

    # Get the one and only service we could be looking at
    $serviceNames{$serviceID} = BookPub::DB::Item::Service->Lookup( service_id => $serviceID )->service_name;

    # For some services, we want to compare the date in the file name against a date in a sale record.
    # So let's grab a sample sale now.
    my $sale = BookPub::DB::Item::Sale->Lookup( file_id => $fileID );
    my $custRef;
    my $custRefError;

    if ( $sale ) {
        my $saleDate = $sale->date_end;

        ( $custRef, $custRefError ) = $self->_getCustRef( $serviceID, $fileName, $saleDate, $fileID );

        # The _getCustRef method does some validation, so we just need to see if we got an error message back.
        if ($custRefError) {
            $validation{isValid} = undef;

            # CustRef is where MMUS stores the reporting period (and some other stuff).
            # If we got one back along with an error message, that means it's invalid.
            if ($custRef) {
                $validation{"Reporting Period"} = "invalid";
            } else

            # If we didn't get one back, then we just couldn't find it.
            {
                $validation{"Reporting Period"} = "missing";
            }
        }
    }

    # Now we need to check for the SAN, which is assigned at the sale level.
    # And let's also check for the Comments, because that could throw an error too.
    my $sales = BookPub::DB::Item::Sale->GetAllByFileID($fileID);

    my $i = 1;
    my %seen;

    while ( my $sale = $sales->next() ) {
        my $countryCode = $self->_normalizeCountryCode( $sale->country_code );
        my ( $clientService, %sanCriteria ) = $self->_getClientService( $sale, $countryCode, $fileName );

        if ( !$clientService ) {
            $validation{isValid} = undef;

            # We need to trim this down to the unique sets of SAN criteria.
            my $seenString = $sale->service_id . "-";
            foreach my $criteria ( sort keys %sanCriteria ) {
                $seenString .= $criteria . "-" . $sanCriteria{$criteria} . "-";
            }

            if ( !$seen{$seenString} ) {
                foreach my $criteria ( sort keys %sanCriteria ) {
                    $validation{"MultipleSANs"}{$i}{$criteria} = $sanCriteria{$criteria};
                }

                $i++;
                $seen{$seenString} = 1;
            }
        }

        my ( $custRef, $custRefError ) = $self->_getSaleCustRef( $serviceID, $sale, $fileName, $custRef );

        if ($custRefError) {
            $validation{isValid} = undef;
            $validation{$custRefError} = "missing";
        }

        my ( $comments, $commentsError ) = $self->_getComments( $sale, $countryCode, $fileName, $custRef );

        if ($commentsError) {
            $validation{isValid} = undef;
            if ( $commentsError eq 'Comments Length' ) {
                $validation{"Comments Length"} = "invalid";
            } else {
                $validation{$commentsError} = "missing";
            }
        }
    }

    return %validation;
}

sub _getSaleCustRef {
    my ( $self, $serviceID, $sale, $fileName, $custRef ) = @_;

    my $error;

    if ( $serviceID == BookPub::Tracker::Service::FOLLETT_HIGHER_EDUCATION() ) {
        my $comment = $sale->r_comments || "";
        if ( $comment eq "MAC" ) {
            $custRef .= " TRADE";
        } else {
            $custRef .= "HE";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::OPEN_ROAD_MEDIA() ) {
        my $comment = $sale->r_comments || "";
        $custRef = "BATCH" . $comment . "-" . $custRef;
    } elsif ( $serviceID == BookPub::Tracker::Service::AMAZON() ) {
        if ( $_sale_file_version eq 'amazon_66' ) {
            my ($saleYear, $saleMonth) = $sale->date_end =~ /(\d{4})-(\d{2})/;
            $saleMonth = uc( $MONTHS[$saleMonth - 1] );

            my $countryCode = uc( $sale->country_code );
            my %territoryMap = (
                DE => 'EU',
                ES => 'EU',
                FR => 'EU',
                IT => 'EU',
                GB => 'UK',
                CA => 'CAD',
            );
            my $territory = exists $territoryMap{$countryCode}
                ? $territoryMap{$countryCode}
                : $countryCode;
            $custRef = "KEP$territory-$saleMonth$saleYear";
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::LSI() && $_sale_file_version eq 'lsi_2' ) {
        if ( my ($y, $m) = $fileName =~ /^sales[ _]comp[ _]LSI[ _](?:GLOBAL|POD[ _]Wholesale)[ _][A-Z]{3}[ _]\d{7}[ _](\d{4})[ _](\d{2})/i ) {
            my ($base, $tail) = split /-/, $sale->r_distributor, 2;
            my $pubCode = join('', $base =~ /\b([A-Za-z])/g);
            $pubCode .= $1 if defined $tail && $tail =~ /^(\d*[A-Za-z])/;

            my $month = $m * 1;
            my $year  = substr($y, -2);
            $custRef = $pubCode . $sale->currency_code . $month . $year;
        }
    }

    return ( $custRef, $error );
}

1;