package BookPub::Sale::Report::Royalty::SagePub;

use strict;
use Template;
use Data::Dumper;
use Text::CSV;
use Date::Calc qw( Add_Delta_Days Days_in_Month );

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::Sale;
use BookPub::DB::Item::File;
use BookPub::DB::Item::Imprint;
use BookPub::DB::Item::Publisher;
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/';
use constant kZipProg    => '/usr/bin/zip';

# We're going to store some values in this so that we don't have to hit the database every time.
my %productData;

sub new {
    my $class = shift;

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

    $self->_init(@_);

    return $self;
}

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

    if ( !$self->{_baseDir} ) {
        my $config = BookPub::Config->new();
        my $client = Common::Client::Current();

        $self->{_baseDir} = $config->get('sale_report_dir');
        $self->{_baseDir} .= '/' . $client->ClientNameClean();
        $self->{_baseDir} .= '/' . $self->{_periodID};
    }
    return $self->{_baseDir};
}

sub fileNameHeader {
    my ($self) = @_;
    my $periodName;
    return "SAGE_HEADER_" . $self->{_periodName} . "_" . $self->{_periodID} . ".tsv";
}

sub fileNameDetail {
    my ($self) = @_;
    my $periodName;
    return "SAGE_DETAIL_" . $self->{_periodName} . "_" . $self->{_periodID} . ".tsv";
}

sub fileNameCognos {
    my ($self) = @_;
    my $periodName;
    return "SAGE_Cognos_" . $self->{_periodName} . "_" . $self->{_periodID} . ".tsv";
}

# I wanted to name this filePathHeader for consistency, but other parts of the system
# are really expecting it to be named filePath.
sub filePath {
    my ($self) = @_;
    if ( !$self->{_filePath} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePath} = $baseDir . '/' . $self->fileNameHeader();
    }
    return $self->{_filePath};
}

sub filePathDetail {
    my ($self) = @_;
    if ( !$self->{_filePathDetail} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathDetail} = $baseDir . '/' . $self->fileNameDetail();
    }
    return $self->{_filePathDetail};
}

sub filePathCognos {
    my ($self) = @_;
    if ( !$self->{_filePathCognos} ) {
        my $baseDir = $self->_baseDir();
        $self->{_filePathCognos} = $baseDir . '/' . $self->fileNameCognos();
    }
    return $self->{_filePathCognos};
}

sub zipFileName {
    my ($self) = @_;
    if ( !$self->{_zipFileName} ) {
        $self->{_zipFileName} = "SAGE_RoyaltyReport_" . $self->{_periodName} . ".zip";
    }
    return $self->{_zipFileName};
}

sub zipFilePath {
    my ($self) = @_;
    if ( !$self->{_zipFilePath} ) {
        my $baseDir = $self->_baseDir();
        $self->{_zipFilePath} = $baseDir . '/' . $self->zipFileName();
    }
    return $self->{_zipFilePath};
}

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 $period = BookPub::DB::Item::Period->Lookup( period_id => $self->{_periodID} );
    my $periodName = $period->name();
    $periodName =~ s/\s+/_/g;
    $periodName =~ s/\W+//g;
    $periodName =~ s/__/_/g;

    $self->{_periodName} = $periodName;
}

sub create {
    my $self = shift;

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

    # First, let's make sure our output files have a home.
    my $baseDir = $self->_baseDir();

    # Making sure the group and owner are set to apache,
    # even when this is manually kicked off.
    my $uid = getpwnam "apache";
    my $gid = getgrnam "apache";

    # We have to check at a couple of levels because of the way we store these reports.
    my @baseDirPath = split( '/', $baseDir );
    pop(@baseDirPath);
    my $superBaseDir = join( '/', @baseDirPath );

    unless ( -e $superBaseDir or mkdir( $superBaseDir, 0755 ) ) {
        die "Unable to create $superBaseDir\n";
    }
    chown $uid, $gid, $superBaseDir;

    unless ( -e $baseDir or mkdir( $baseDir, 0755 ) ) {
        die "Unable to create $baseDir\n";
    }

    chown $uid, $gid, $baseDir;

    #
    # Now let's create the header file.
    #

    Common::Log::Print("Creating header file");
    my $outFilePathHeader = $self->filePath();

    if ( -e $outFilePathHeader ) {
        Common::Log::Print("$outFilePathHeader exists, removing it...");
        die "\nCan't remove $outFilePathHeader\n" unless ( unlink $outFilePathHeader );
    }

    #########################################################
    # We don't want to create a header file anymore. RSD-8084
    #########################################################
    # unless ( open( OUT, '>:utf8', $outFilePathHeader ) ) {
    #     die "can't open output file ($outFilePathHeader) - $!\r\n";
    # }
    #
    # chown $uid, $gid, $outFilePathHeader;

    # Print the main header
    my @headers = (
        'company_code',   'process_ctrl_num', 'source_ctrl_num',     'order_ctrl_num',  'trx_ctrl_num',    'doc_desc',
        'doc_ctrl_num',   'apply_to_num',     'apply_trx_type',      'trx_type',        'date_applied',    'date_doc',
        'date_shipped',   'date_due',         'date_aging',          'customer_code',   'ship_to_code',    'salesperson_code',
        'territory_code', 'comment_code',     'posting_code',        'terms_code',      'cust_po_num',     'hold_flag',
        'hold_desc',      'recurring_flag',   'recurring_code',      'tax_code',        'nat_cur_code',    'rate_type_home',
        'rate_type_oper', 'rate_home',        'rate_oper',           'prepay_discount', 'prepay_amt',      'prepay_doc_num',
        'prompt1',        'prompt2',          'prompt3',             'prompt4',         'amt_paid',        'amt_rem_rev',
        'amt_rem_tax',    'amt_tax_included', 'amt_write_off_given', 'attention_name',  'attention_phone', 'customer_addr1',
        'customer_addr2', 'customer_addr3',   'customer_addr4',      'customer_addr5',  'customer_addr6',  'date_entered',
        'date_recurring', 'date_required',    'dest_zone_code',      'fin_chg_code',    'fob_code',        'freight_code',
        'location_code',  'price_code',       'printed_flag',        'ship_to_addr1',   'ship_to_addr2',   'ship_to_addr3',
        'ship_to_addr4',  'ship_to_addr5',    'ship_to_addr6',       'source_trx_type', 'total_weight',    'amt_freight',
        'processed_flag', '',                 'batch_no',            'record_id_num',   'User_ID',         'writeoff_code',
        'vat_prc',        'org_id'
    );
    # print OUT join( "\t", @headers ), "\n";

    # Might as well cache this since we'll need them again later.
    my %fileData;
    my %serviceNames;

    # Alright, let's get the files for this period.
    my $periodID = $self->periodID();
    my $files = BookPub::DB::Item::File->GetByPeriodID( $periodID, sort_by => 'service_name');

    while ( my $file = $files->next() ) {
        my $fileID    = $file->file_id;
        my $fileName  = $file->orig_file_name;
        my $serviceID = $file->service_id;

        my $sales = BookPub::DB::Item::Sale->GetAllByFileID($fileID);
        while ( my $sale = $sales->next() ) {
            my $saleID = $sale->sale_id;

            # We need to grab the service id from a sale if they are using the royaltyshare template or
            # it's coming from something resembling a distributor
            if (   $serviceID == BookPub::Tracker::Service::RSFORMAT()
                || $serviceID == BookPub::Tracker::Service::VITAL_SOURCE() ) {
                $serviceID = $sale->service_id;
            }

            my ( $clientServiceID, %sanCriteria ) = $self->_getClientServiceID( $serviceID, $fileName, $sale );
            if ( !$clientServiceID ) {
                my $error = "No match found in client service table for file: " . $fileName;
                $self->_sendErrorEmail($error);
                die $error . "\n";
            }

            # First 3 characters of client service ID followed by the first 13 characters of the file name.
            my $controlNumber = substr( $clientServiceID, 0, 3 ) . substr( $fileName, 0, 13 );
            my $longControlNumber = $clientServiceID . $fileName;


            # If there are sales and returns, this gets set to 2031. If there are only returns, set it to 2032.
            my $hasSales = BookPub::DB::Item::File->HasSales($fileID);
            my $transactionTypes = $hasSales ? 2031 : 2032;

            my $dateUploaded = $self->_formatDate( $file->date_created );
            my $dateDue = $self->_formatDate( $file->date_created, 60 );

            # This is a weird one.  It's the max date end from the file, but it can't fall outside of the report period
            # so we may need to normalize it.
            my $dateDoc = $self->_getDateDoc($fileID);

            my @detailLine = (
                '1', '', $longControlNumber, $controlNumber, '', 'E-Books Sales', '', '', '0', $transactionTypes, $dateUploaded, $dateDoc,
                $dateUploaded, $dateDue, $dateDue, $clientServiceID, '', 'NONE', 'CORP', '', 'ebooks', '60', '', '0', 'NO HOLD', '0',
                'MONTHLY', 'DEFAULT', 'USD', 'BUY', 'BUY', '1', '1', '0', '0', '', substr( $fileName, 0, 30 ), substr( $fileName, 30, 30 ), '',
                '', '0', '0', '0', '0', '0', '', '', '', '', '', '', '', '', $dateUploaded, '', '', '', '', '', '', '', '', '1', '', '', '',
                '', '', '', '0', '0', '0', '0', '', '0', '0', '', 'DEFAULT', '0', '1'
            );
            # print OUT join( "\t", @detailLine ), "\n";

            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;
            }

            # Since we're storing file level data already, I'm going to add a couple of
            # things that we only # need for the Cognos file now.
            my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

            # If we still don't have these, time to die.
            if ( !$reportingMonth && !$reportingQuarter ) {
                my $error = "Unable to determine reporting period for file ID: " . $fileID;
                $self->_sendErrorEmail($error);
                die $error . "\n";
            }

            $fileData{$fileID}{$saleID}{fileName}          = $file->orig_file_name;
            $fileData{$fileID}{$saleID}{serviceName}       = $serviceName;
            $fileData{$fileID}{$saleID}{longControlNumber} = $longControlNumber;
            $fileData{$fileID}{$saleID}{controlNumber}     = $controlNumber;
            $fileData{$fileID}{$saleID}{clientServiceID}   = $clientServiceID;
            $fileData{$fileID}{$saleID}{dateUploaded}      = $dateUploaded;
            $fileData{$fileID}{$saleID}{transactionTypes}  = $transactionTypes;
            $fileData{$fileID}{$saleID}{reportingMonth}    = $reportingMonth;
            $fileData{$fileID}{$saleID}{reportingQuarter}  = $reportingQuarter;
            $fileData{$fileID}{$saleID}{reportingYear}     = $reportingYear;

        }

    }

    # close(OUT);

    #
    # Ok, header file has been generated.
    # Time for the detail file.
    Common::Log::Print("Creating detail file");
    my $outFilePathDetail = $self->filePathDetail();
    if ( -e $outFilePathDetail ) {
        Common::Log::Print("$outFilePathDetail exists, removing it...");
        die "\nCan't remove $outFilePathDetail\n" unless ( unlink $outFilePathDetail );
    }

    ##########################################################
    # We don't want to create a details file anymore. RSD-8084
    ##########################################################
    # unless ( open( OUT, '>:utf8', $outFilePathDetail ) ) {
    #     die "can't open output file ($outFilePathDetail) - $!\r\n";
    # }
    #
    # chown $uid, $gid, $outFilePathDetail;

    # Print the main header
    @headers = (
        'company_code',      'process_ctrl_num', 'source_ctrl_num', 'order_ctrl_num', 'trx_ctrl_num',    'sequence_id',
        'trx_type',          'location_code',    'item_code',       'line_desc',      'qty_ordered',     'qty_shipped',
        'qty_returned',      'unit_code',        'unit_price',      'unit_cost',      'weight',          'tax_code',
        'gl_rev_acct',       'disc_prc_flag',    'discount_amt',    'rma_num',        'return_code',     'reference_code',
        'bulk_flag',         'calc_tax',         'commission_flag', 'date_entered',   'new_gl_rev_acct', 'oe_orig_flag',
        'qty_prev_returned', 'processed_flag',   '',                'record_id_num',  'User_ID',         'cust_po',
        'org_id'
    );
    # print OUT join( "\t", @headers ), "\n";

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

    while ( my $sale = $sales->next() ) {
        my $saleID      = $sale->sale_id;
        my $fileID      = $sale->file_id;
        my $productID   = $sale->product_id;
        my $institution = $sale->r_institution;
        my $units       = $sale->units;
        my $revenue     = $sale->revenue;

        # Let's skip the sales with 0 revenue AND 0 units.
        next if ( $revenue == 0 && $units == 0 );

        my $conversionRate = $sale->conversion_rate || 1;

        # Let's go ahead and make sure the revenue is in USD.
        $revenue *= $conversionRate;

        # Storing these away now for the validation at the end
        $totalUnits            += $units;
        $totalRevenueUnrounded += $revenue;

        # Now that we've updated the totals for validation, we need to look for sales with revenue but no units.
        # We'll add one unit to match the revenue.
        if ( $units == 0 && $revenue > 0 ) {
            $units = 1;
        } elsif ( $units == 0 && $revenue < 0 ) {
            $units = -1;
        }

        my $currencyCode = $sale->currency_code;
        my $countryCode  = $sale->country_code;
        my $listPrice    = abs( $sale->list_price );
        my $unitPrice    = abs( $revenue / $units );

        # Use revenue currency if list price currency was not specified.
        my $listCurrency = $sale->r_list_price_currency || $currencyCode;

        # If list price currency is the same as revenue currency,
        # then we can just apply the same conversion rate to it.
        # Also, if it's USD, we can just use it as-is.
        if ( $listCurrency eq $currencyCode ) {
            $listPrice *= $conversionRate;
        } elsif ( uc($listCurrency) ne 'USD' ) {
            $listPrice = 0;
        }

        my $discount = 0;
        if ( $revenue == 0 ) {
            $discount = 100;
        } elsif ($listPrice) {
            $discount = 1 - ( $unitPrice / $listPrice );
        } else {
            # If we don't have a list price, use the unit price in its place
            # and set the discount to 0.
            $listPrice = $unitPrice;
            $discount  = 0;
        }

        # !!! Maybe these don't need to be rounded for SAGE...
        # !!! Commenting out for now anyway.
        #
        # These all need to be rounded to two decimal places
        #$listPrice = formatFixedPoint($listPrice, 2);
        #$unitPrice = formatFixedPoint($unitPrice, 2);
        #$discount  = formatFixedPoint($discount, 2);

        my $longControlNumber = $fileData{$fileID}{$saleID}{longControlNumber};
        my $controlNumber     = $fileData{$fileID}{$saleID}{controlNumber};
        my $transactionTypes  = $fileData{$fileID}{$saleID}{transactionTypes};
        my $serviceName       = $fileData{$fileID}{$saleID}{serviceName};
        my $dateUploaded      = $fileData{$fileID}{$saleID}{dateUploaded};

        # Need to add something so that the sales and returns units don't aggregate together.
        my $saleType = 'sale';
        if ( $units < 0 ) {
            $saleType = 'return';
        }

        $rows{$serviceName}{$fileID}{$saleID}{$institution}{$saleType}{$longControlNumber}{$controlNumber}{$transactionTypes}{$productID}
          {$countryCode}{$unitPrice}{$listPrice}{$discount}{$dateUploaded}{units} += $units;

    }

    my $superSequence = 1;

    foreach my $serviceName ( sort keys %rows ) {
        my $serviceSales = $rows{$serviceName};

        foreach my $fileID ( sort keys %$serviceSales ) {
            my $fileSales    = $serviceSales->{$fileID};
            my $fileSequence = 1;

            foreach my $saleID ( sort keys %$fileSales ) {
                my $saleIDs = $fileSales->{$saleID};

                foreach my $institution ( sort keys %$saleIDs ) {
                    my $institutionSales = $saleIDs->{$institution};

                    foreach my $saleType ( sort keys %$institutionSales ) {
                        my $saleTypeSales = $institutionSales->{$saleType};

                        foreach my $longControlNumber ( sort keys %$saleTypeSales ) {
                            my $longControlSales = $saleTypeSales->{$longControlNumber};

                            foreach my $controlNumber ( sort keys %$longControlSales ) {
                                my $controlSales = $longControlSales->{$controlNumber};

                                foreach my $transactionTypes ( sort keys %$controlSales ) {
                                    my $transactionTypesSales = $controlSales->{$transactionTypes};

                                    foreach my $productID ( sort keys %$transactionTypesSales ) {
                                        my $productSales = $transactionTypesSales->{$productID};

                                        foreach my $countryCode ( sort keys %$productSales ) {
                                            my $countrySales = $productSales->{$countryCode};

                                            foreach my $unitPrice ( sort keys %$countrySales ) {
                                                my $unitPriceSales = $countrySales->{$unitPrice};

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

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

                                                        foreach my $dateUploaded ( sort keys %$discountSales ) {
                                                            my $row = $discountSales->{$dateUploaded};

                                                            my $units = $row->{units};

                                                            my $bookProduct =
                                                            BookPub::DB::Item::BookProduct->Lookup( product_id => $productID );
                                                            my $isbn13 = $bookProduct->isbn13;

                                                            my $book = BookPub::DB::Item::Book->Lookup( book_id => $bookProduct->book_id );
                                                            my $title = substr( $book->title, 0, 60 );

                                                            my $imprintCode = $self->_getImprintCode( $bookProduct->imprint_id );

                                                            my $onlyReturns = 0;
                                                            if ( $transactionTypes == 2032 ) {
                                                                $onlyReturns = 1;
                                                            }

                                                            my @details = (
                                                                '1',          '',             $longControlNumber, $controlNumber,
                                                                '',           $fileSequence,  $transactionTypes,  $imprintCode,
                                                                $isbn13,      $title,         $units,             $units,
                                                                $onlyReturns, $countryCode,   $unitPrice,         $listPrice,
                                                                '0',          'DEFAULT',      '',                 '0',
                                                                $discount,    $serviceName,   '',                 $institution,
                                                                '0',          '0',            '0',                $dateUploaded,
                                                                '',           '1',            '0',                '0',
                                                                '',           $superSequence, '',                 '',
                                                                '1'
                                                            );

                                                            # print OUT join( "\t", @details ), "\n";

                                                            $fileSequence++;
                                                            $superSequence++;
                                                        }    #dateUploaded
                                                    }    #discount
                                                }    #listPrice
                                            }    #unitPrice
                                        }    #countryCode
                                    }    #productID
                                }    #transactionTypes
                            }    #controlNumber
                        }    #longControlNumber
                    }    #saleType
                }    #fileID
            } # customerSAN
        }    #institution
    }    #serviceName

    Common::Log::Print("Report created: $outFilePathDetail");

    #
    # And finally, let's generate the Cognos file.
    #

    Common::Log::Print("Creating Cognos file");
    my $outFilePathCognos = $self->filePathCognos();

    if ( -e $outFilePathCognos ) {
        Common::Log::Print("$outFilePathCognos exists, removing it...");
        die "\nCan't remove $outFilePathCognos\n" unless ( unlink $outFilePathCognos );
    }

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

    chown $uid, $gid, $outFilePathCognos;

    # Print the main header
    @headers = (
        'Reporting Month',
        'Reporting Quarter',
        'Reporting Year',
        'Transaction Date',
        'Service Name',
        'ISBN 13',
        'Vendor ID',
        'Title',
        'Author Name',
        'Publisher',
        'Imprint Name',
        'Classification Code',
        'Product Type',
        'Purchase Type',
        'Original Rental Duration',
        'New Rental Duration',
        'Units',
        'Transaction Type',
        'List Price',
        'List Price Currency',
        'Purchase Price',
        'Purchase Price Currency',
        'Reported Discount',
        'Actual Discount',
        'Net Payment USD',
        'Net Payment',
        'Net Payment Currency',
        'State',
        'Postal Code',
        'Country Code',
        'Institution',
        'System User	Model',
        'Channel Name',
        'Transaction Category',
        'File Name',
        'CUSTOMER',
    );
    print OUT join( "\t", @headers ), "\n";

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

    my %report_CAD_revenue = (
        "CUS05584"     => 1, # VitalSource Athabasca University
        "CUS12843"     => 1, # Campus eBookstore
        "CUS03254"     => 1, # eConcordia
        "FOLTCAFE"     => 1, # Follett-Higher-Education
        "UK1000949483" => # Google
    );

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

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

        # Let's skip the sales with 0 revenue AND 0 units.
        if ( $revenue == 0 && $units == 0 ) {
            next;
        }

        my $conversionRate = $sale->conversion_rate;
        if ( !$conversionRate ) {
            $conversionRate = 1;
        }

        my $customerSAN = $fileData{$fileID}{$saleID}{clientServiceID};
        my $netPaymentCurrency = $sale->currency_code;
        my $netPayment = sprintf("%.2f",$revenue);

        # Let's go ahead and make sure the revenue is in USD.
        $revenue *= $conversionRate;

        # We want to report original CAD revenue in column Z for certain SANs
        $netPayment = $revenue if (!exists($report_CAD_revenue{$customerSAN}));
        $netPaymentCurrency = 'USD' if (!exists($report_CAD_revenue{$customerSAN}));

        # Now that we've updated the totals for validation, we need to look for sales with revenue but no units.
        # We'll add one unit to match the revenue.
        if ( $units == 0 && $revenue > 0 ) {
            $units = 1;
        } elsif ( $units == 0 && $revenue < 0 ) {
            $units = -1;
        }

        my $listPrice = abs( $sale->list_price );

        # Use revenue currency if list price currency was not specified.
        my $listPriceCurrency = $sale->r_list_price_currency;
        if ( !$listPriceCurrency && $listPrice ) {
            $listPriceCurrency = $sale->currency_code;
        }

        my $netDiscount;

        if ( $revenue == 0 ) {
            $netDiscount = 100;
        } elsif ($listPrice) {
            my $unitPrice = abs( $revenue / $units );
            $netDiscount = 1 - ( $unitPrice / $listPrice );
        }

        # We only want to populate transaction date if we have a single date and not a range.
        my $transactionDate;
        if ( $sale->date_begin eq $sale->date_end ) {

            # Let's make sure this is a valid date.
            my $dateError = $self->_validateDate( $sale->date_begin );

            if ($dateError) {
                my $file = BookPub::DB::Item::File->Lookup( file_id => $sale->file_id );
                my $fileName = $file->orig_file_name;

                $dateError .= " -- File: $fileName";
                $self->_sendErrorEmail($dateError);
                die $dateError . "\n";
            }

            $transactionDate = $self->_formatDate( $sale->date_begin );
        }

        # Need to keep sales, returns, and free units separate.
        my $saleType;
        if ( $units > 0 && $revenue > 0 ) {
            $saleType = 'Sale';
        } elsif ( $units > 0 && $revenue == 0 ) {
            $saleType = 'Gratis';
        } else {
            $saleType = 'Return';
        }

        # And now just a bunch of fields that we're grabbing the raw data from
        my $vendorID            = $sale->service_product_id;
        my $institution         = $sale->r_institution;
        my $rentalType          = $sale->r_purchase_type;
        my $originalDuration    = $sale->r_previous_duration;
        my $duration            = $sale->r_duration;
        my $discount            = $sale->r_discount;
        my $systemUser          = $sale->r_delivery_method;
        my $model               = $sale->r_model;
        my $channel             = $sale->r_channel;
        my $transactionCategory = $sale->r_transaction_category;
        my $purchasePrice       = $sale->r_purchase_price;
        if ( $purchasePrice < 0 ) {
            $purchasePrice *= -1;
        }
        my $purchasePriceCurrency = $sale->r_purchase_price_currency;
        my $countryCode           = $sale->country_code;
        my $state                 = $sale->state;
        my $postalCode            = $sale->postal_code;

        my $serviceName = $fileData{$fileID}{$saleID}{serviceName};

        # Things we're going to look up later with the product id:
        # isbn13, title, author, publisher, imprint, product type

        # Things we're going to look up later with the file id:
        # reporting month, reporting quarter, reporting year, file name

        $cognosRows{$serviceName}{$fileID}{$saleID}{$saleType}{$productID}{$vendorID}{$transactionDate}{$rentalType}{$originalDuration}{$duration}
          {$listPriceCurrency}{$listPrice}{$purchasePriceCurrency}{$purchasePrice}{$discount}{$netDiscount}{$countryCode}{$state}
          {$postalCode}{$institution}{$systemUser}{$model}{$channel}{$transactionCategory}{units} += $units;

        $cognosRows{$serviceName}{$fileID}{$saleID}{$saleType}{$productID}{$vendorID}{$transactionDate}{$rentalType}{$originalDuration}{$duration}
          {$listPriceCurrency}{$listPrice}{$purchasePriceCurrency}{$purchasePrice}{$discount}{$netDiscount}{$countryCode}{$state}
          {$postalCode}{$institution}{$systemUser}{$model}{$channel}{$transactionCategory}{revenue} += $revenue;

        $cognosRows{$serviceName}{$fileID}{$saleID}{$saleType}{$productID}{$vendorID}{$transactionDate}{$rentalType}{$originalDuration}{$duration}
          {$listPriceCurrency}{$listPrice}{$purchasePriceCurrency}{$purchasePrice}{$discount}{$netDiscount}{$countryCode}{$state}
          {$postalCode}{$institution}{$systemUser}{$model}{$channel}{$transactionCategory}{netPayment} += $netPayment;

        $cognosRows{$serviceName}{$fileID}{$saleID}{$saleType}{$productID}{$vendorID}{$transactionDate}{$rentalType}{$originalDuration}{$duration}
          {$listPriceCurrency}{$listPrice}{$purchasePriceCurrency}{$purchasePrice}{$discount}{$netDiscount}{$countryCode}{$state}
          {$postalCode}{$institution}{$systemUser}{$model}{$channel}{$transactionCategory}{netPaymentCurrency} = $netPaymentCurrency;
    }

    foreach my $serviceName ( sort keys %cognosRows ) {
        my $serviceSales = $cognosRows{$serviceName};

        foreach my $fileID ( sort keys %$serviceSales ) {
            my $fileSales = $serviceSales->{$fileID};

            foreach my $saleID ( sort keys %$fileSales ) {
                my $saleIDs = $fileSales->{$saleID};

                foreach my $saleType ( sort keys %$saleIDs ) {
                    my $saleTypeSales = $saleIDs->{$saleType};

                    foreach my $productID ( sort keys %$saleTypeSales ) {
                        my $productSales = $saleTypeSales->{$productID};

                        foreach my $vendorID ( sort keys %$productSales ) {
                            my $vendorSales = $productSales->{$vendorID};

                            foreach my $transactionDate ( sort keys %$vendorSales ) {
                                my $transactionDateSales = $vendorSales->{$transactionDate};

                                foreach my $rentalType ( sort keys %$transactionDateSales ) {
                                    my $rentalSales = $transactionDateSales->{$rentalType};

                                    foreach my $originalDuration ( sort keys %$rentalSales ) {
                                        my $originalDurationSales = $rentalSales->{$originalDuration};

                                        foreach my $duration ( sort keys %$originalDurationSales ) {
                                            my $durationSales = $originalDurationSales->{$duration};

                                            foreach my $listPriceCurrency ( sort keys %$durationSales ) {
                                                my $listPriceCurrencySales = $durationSales->{$listPriceCurrency};

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

                                                    foreach my $purchasePriceCurrency ( sort keys %$listPriceSales ) {
                                                        my $purchasePriceCurrencySales = $listPriceSales->{$purchasePriceCurrency};

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

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

                                                                foreach my $netDiscount ( sort keys %$discountSales ) {
                                                                    my $netDiscountSales = $discountSales->{$netDiscount};

                                                                    foreach my $countryCode ( sort keys %$netDiscountSales ) {
                                                                        my $countrySales = $netDiscountSales->{$countryCode};

                                                                        foreach my $state ( sort keys %$countrySales ) {
                                                                            my $stateSales = $countrySales->{$state};

                                                                            foreach my $postalCode ( sort keys %$stateSales ) {
                                                                                my $postalSales = $stateSales->{$postalCode};

                                                                                foreach my $institution ( sort keys %$postalSales ) {
                                                                                    my $institutionSales = $postalSales->{$institution};

                                                                                    foreach my $systemUser ( sort keys %$institutionSales ) {
                                                                                        my $systemUserSales = $institutionSales->{$systemUser};

                                                                                        foreach my $model ( sort keys %$systemUserSales ) {
                                                                                            my $modelSales = $systemUserSales->{$model};

                                                                                            foreach my $channel ( sort keys %$modelSales ) {
                                                                                                my $channelSales = $modelSales->{$channel};

                                                                                                foreach my $transactionCategory (
                                                                                                    sort keys %$channelSales ) {
                                                                                                    my $row = $channelSales->{$transactionCategory};


                                                                                                    my $units   = $row->{units};
                                                                                                    my $revenue = $row->{revenue};

                                                                                                    my ( $isbn, $title, $author, $imprint, $publisher, $productType ) = $self->_getProductData($productID);

                                                                                                    my $fileName         = $fileData{$fileID}{$saleID}{fileName};
                                                                                                    my $reportingMonth   = $fileData{$fileID}{$saleID}{reportingMonth};
                                                                                                    my $reportingQuarter = $fileData{$fileID}{$saleID}{reportingQuarter};
                                                                                                    my $reportingYear    = $fileData{$fileID}{$saleID}{reportingYear};
                                                                                                    my $customerSAN      = $fileData{$fileID}{$saleID}{clientServiceID};


                                                                                                    my @details = (
                                                                                                        $reportingMonth,
                                                                                                        $reportingQuarter,
                                                                                                        $reportingYear,
                                                                                                        $transactionDate,
                                                                                                        $serviceName,
                                                                                                        $isbn,
                                                                                                        $vendorID,
                                                                                                        $title,
                                                                                                        $author,
                                                                                                        $publisher,
                                                                                                        $imprint,
                                                                                                        '',
                                                                                                        $productType,
                                                                                                        $rentalType,
                                                                                                        $originalDuration,
                                                                                                        $duration,
                                                                                                        $units,
                                                                                                        $saleType,
                                                                                                        $listPrice,
                                                                                                        $listPriceCurrency,
                                                                                                        $purchasePrice,
                                                                                                        $purchasePriceCurrency,
                                                                                                        $discount,
                                                                                                        $netDiscount,
                                                                                                        $revenue,
                                                                                                        $row->{netPayment},
                                                                                                        $row->{netPaymentCurrency},
                                                                                                        $state,
                                                                                                        $postalCode,
                                                                                                        $countryCode,
                                                                                                        $institution,
                                                                                                        $systemUser,
                                                                                                        $model,
                                                                                                        $channel,
                                                                                                        $transactionCategory,
                                                                                                        $fileName,
                                                                                                        $customerSAN
                                                                                                    );

                                                                                                    print OUT join( "\t", @details ), "\n";
                                                                                                }
                                                                                            }
                                                                                        }
                                                                                    }
                                                                                }
                                                                            }
                                                                        }
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    Common::Log::Print("Report created: $outFilePathCognos");

    #
    # Okay, let's go ahead and create the zip file since we need to FTP it.
    #

    my $zipFile = $self->zipFilePath();

    if ( -e $zipFile ) {
        Common::Log::Print("$zipFile exists, removing it...");
        die "\nCan't remove $zipFile\n" unless ( unlink $zipFile );
    }

    # Change to that directory, so the compressed file won't have a bunch of extraneous
    # directories in it.
    #
    chdir($baseDir) || die "ERROR - unable to change directories to $baseDir: $!";

    my $zipCmd = kZipProg . ' -r';

    my $zipFileName = $self->zipFileName();

    system( $zipCmd . " " . $zipFileName . " ." );

    chown $uid, $gid, $zipFile;

    Common::Log::Print("Zip file created: $zipFile");

    # 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 _getClientServiceID {
    my ( $self, $serviceID, $fileName, $sale ) = @_;

    my $clientService;
    my $clientServiceID;

    # We need to send a back the list of fields and their values that we used to look for the SAN for a service.
    # Service ID is always a criteria, so you don't need to add that in here _unless_ it is the only criteria.
    my %sanCriteria;

    if ( $serviceID == BookPub::Tracker::Service::AMAZON() ) {
        $sanCriteria{'Country Code'} = 'missing';

        if ( $fileName =~ /^(SAGEU_)DigitalEBooksPaymentReport_\d{8}-\d{8}_([A-Z]{2})\.\w+$/i ) {
            my $feedID = uc($1 . $2);
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feedID );
        }
        # We need to look these up by country
        elsif ( $fileName =~ /^\w{5}[ _](?!digi)(\w\w)/i || $fileName =~ /^\w{5}[\s_]Rental[\s_](\w{2})/i || $fileName =~ /^\w{5}[ _]digi.+?[ _](\w\w)(?:[ _]\w{3})?\./i) {
            my $countryCode = uc($1);
            $sanCriteria{'Country Code'} = $countryCode;
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $countryCode );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::MBS_TEX ) {
        my $feed_id;
        if ( $fileName =~ /(D_X_16862443)/i ) {
            $feed_id = uc($1);
        } else {
            $feed_id = 'ANY';    # the default MBS SAN
        }
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::UNIZIN ) {
        my $feed_id;
        if ( $fileName =~ /Berkeley/i ) {
            $feed_id = 'Berkeley';
        }
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::VITAL_SOURCE ) {
        my $feed_id;
        if ( $fileName =~ /(BNED)/i ) {
            $feed_id = uc($1);
        } elsif ( $fileName =~ /(Kaplan)/i ) {    #FB14971
            $feed_id = uc($1);
        } elsif ( $fileName =~ /WGU|VST Usage Report/i ) {
            $feed_id = "WGU";
        } elsif ( $fileName =~ /(Capella|Athabasca|Ashworth)/i ) {    # RSD-6862, RSD-6863, RSD-6865
            $feed_id = $1;
        } elsif ( $fileName =~ /Northeastern \d{1,2}\.\d{2}/i ) {
            $feed_id = "NJC";
        } else {
            $feed_id = 'VITAL_SOURCE';
        }
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::CHEGG ) {
        if ( $fileName =~ /Ebook[ _]Subscription/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup( client_service_id => 'CHG-SUB', service_id => $serviceID );
        } else {
            $clientService = BookPub::DB::Item::ClientService->Lookup( client_service_id => 'CHG-EBK', service_id => $serviceID );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::GOOGLE ) {
        if ( $fileName =~ /(?<![A-Za-z0-9])UK(?![A-Za-z0-9])/i ) {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID , feed_id => 'UK' );
        }
        else {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::EBSCO ) {
        my $rImprint = $sale->r_imprint;
        if ( $rImprint =~ /Sage Publications, Ltd/i ) {
            # take SAN for GB as long as imprint reads 'Sage Publications, Ltd'
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => 'GB' );
        } else {
            $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID );
        }
    } elsif ( $serviceID == BookPub::Tracker::Service::PROQUEST || $serviceID == BookPub::Tracker::Service::PROQUEST_SUBSCRIPTION ) {
        my $feed_id = undef;
        $feed_id = 'UK' if ( $fileName =~ /[ _-]1135/i );
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID , feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::CREDO ) {
        my $countryCode = $sale->country_code;
        my $feed_id;
        $feed_id = 'CREDO-UK' if ( $countryCode =~ /^(?:GB|UK)$/i );
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::GALE ) {
        my $countryCode = $sale->country_code;
        my $feed_id;
        $feed_id = 'GALE-UK' if ( $countryCode =~ /^(?:GB|UK)$/i );
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::EBOOK ) {
        my $feed_id;
        $feed_id = 'EBOOKS-UK' if ( $fileName =~ /SagePublicationsLTD/i );
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID == BookPub::Tracker::Service::KOBO ) {
        my $feed_id;
        $feed_id = 'KOBO-UK' if ( $fileName =~ /SAGEUK/i );
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } elsif ( $serviceID ==BookPub::Tracker::Service::NORTHWEST_MISSOURI_STATE_UNIVERSITY ) {
        my $feed_id = 'RSFMT_NMSU';
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID, feed_id => $feed_id );
    } else {
        $sanCriteria{'Service'} = $serviceID;
        $clientService = BookPub::DB::Item::ClientService->Lookup( service_id => $serviceID );
    }

    if ($clientService) {
        $clientServiceID = $clientService->client_service_id;
    }

    return ( $clientServiceID, %sanCriteria );
}

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

    my ( $dateBegin, $dateEnd ) = BookPub::DB::Item::Sale->GetDateRangeByFileID($fileID);

    # Let's make sure these are valid dates.
    my $dateError = $self->_validateDate($dateBegin);

    if ( !$dateError ) {
        $dateError = $self->_validateDate($dateEnd);
    }

    if ($dateError) {
        my $file = BookPub::DB::Item::File->Lookup( file_id => $fileID );
        my $fileName = $file->orig_file_name;

        $dateError .= " -- File: $fileName";
        $self->_sendErrorEmail($dateError);
        die $dateError . "\n";
    }

    my $dateBeginMonth = substr( $dateBegin, 5, 2 );
    my $dateEndMonth   = substr( $dateEnd,   5, 2 );

    # If these do not fall in the same month, try normalizing it.
    if ( $dateBeginMonth != $dateEndMonth ) {
        my ( $dateBeginNormal, $dateEndNormal ) = Common::Util::normalize_sales_month_dates( $dateBegin, $dateEnd );

        my $dateEndNormalMonth = substr( $dateEndNormal, 5, 2 );

        # If the normalize date end month is different from the old date end month,
        # use the normalized date instead.
        if ( $dateEndMonth != $dateEndNormalMonth ) {
            $dateEnd = $dateEndNormal;
        }
    }

    return $dateEnd;
}

sub _getDateFromFilename() {
    my $self   = shift;
    my $fileID = shift;

    my $fileObj   = BookPub::DB::Item::File->Lookup( file_id => $fileID );
    my $fileName  = $fileObj->orig_file_name;
    my $serviceID = $fileObj->service_id;
    my ( $reportingMonth, $reportingYear );

    my %months = (
        january   => 1,
        february  => 2,
        march     => 3,
        april     => 4,
        may       => 5,
        june      => 6,
        july      => 7,
        august    => 8,
        september => 9,
        october   => 10,
        november  => 11,
        december  => 12,
    );

    if ( $serviceID == BookPub::Tracker::Service::APUS() ) {
        if ( $fileName =~ m/(\w{3,9})[\s_](\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::CHEGG() ) {
        if ( $fileName =~ m/^(\d{4})-(\d{2})/ ) {
            $reportingYear  = $1;
            $reportingMonth = $2;
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::ED_MAP() ) {
        if ( $fileName =~ m/(\d{2})(\d{4})/ ) {
            $reportingYear  = $2;
            $reportingMonth = $1;
        }
        if ( $fileName =~ m/^(\w{3,9}) (\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::GOOGLE() ) {
        if ( $fileName =~ m/(\d{4})[\s_](\d{2})/ ) {
            $reportingYear  = $1;
            $reportingMonth = $2;
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::KOBO() ) {
        if ( $fileName =~ m/(\w{3,9}) (\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::RSFORMAT() ) {
        if ( $fileName =~ m/(\d{4})_(\d{2}) and (\d{2})/ ) {
            $reportingYear  = $1;
            $reportingMonth = $3;
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::VIRDOCS() )    #REDSHELF
    {
        if ( $fileName =~ m/(\d{4})_(\w{3,9})/ ) {
            $reportingYear = $1;
            $reportingMonth = sprintf( "%02d", $months{ lc($2) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::WESTERN_INTERNATIONAL() ) {
        if ( $fileName =~ m/(\d{2})_(\d{4})/ ) {
            $reportingYear  = $2;
            $reportingMonth = $1;
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::YUZU() ) {
        if ( $fileName =~ m/(\w{3,9}) (\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::NEMCC() ) {
        if ( $fileName =~ m/(\w{3,9}) (\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }
    if ( $serviceID == BookPub::Tracker::Service::YOUNGSTOWN_STATE() ) {
        if ( $fileName =~ m/(\w{3,9}) (\d{4})/ ) {
            $reportingYear = $2;
            $reportingMonth = sprintf( "%02d", $months{ lc($1) } );
        }
    }

    return ( $reportingMonth, $reportingYear );
}

sub _getImprintCode {
    my ( $self, $imprintID ) = @_;

    my %imprintCodes;

    $imprintCodes{7}  = 'A';    # Arnold SS
    $imprintCodes{9}  = 'D';    # Arnold STMS
    $imprintCodes{2}  = 'C';    # Corwin
    $imprintCodes{5}  = 'Q';    # CQ Press
    $imprintCodes{8}  = 'B';    # IMECHE
    $imprintCodes{6}  = 'M';    # Learning Matters
    $imprintCodes{10} = 'U';    # Paul Chapman Educational Publishing
    $imprintCodes{11} = 'P';    # Pine Forge Press
    $imprintCodes{12} = 'H';    # RSM
    $imprintCodes{13} = 'T';    # SAGE Ltd Science
    $imprintCodes{3}  = 'L';    # SAGE Publications Ltd
    $imprintCodes{4}  = 'I';    # SAGE Publications Pvt. Ltd
    $imprintCodes{1}  = 'S';    # SAGE Publications, Inc
    $imprintCodes{14} = 'N';    # SAGE Science Press
    $imprintCodes{15} = 'R';    # Scolari

    if ( $imprintCodes{$imprintID} ) {
        return $imprintCodes{$imprintID};
    } else {

        # If we don't have a location code, just return the first 8 characters of the imprint name.
        my $imprint = BookPub::DB::Item::Imprint->Lookup( imprint_id => $imprintID );
        return substr( $imprint->name, 0, 8 );
    }
}

sub _formatDate {
    my ( $self, $date, $offset ) = @_;

    my $formattedDate;

    # Dates will be passed in ISO format and need to be returned in MM/DD/YYYY
    # Also, there may be an offset that we need to apply.

    if ( !$offset ) {
        $offset = 0;
    }

    if ( $date =~ /^(\d{4})-(\d{2})-(\d{2})/ ) {
        my ( $year, $month, $day ) = Add_Delta_Days( $1, $2, $3, $offset );
        $formattedDate = $month . "/" . $day . "/" . $year;
    }

    return $formattedDate;
}

sub _getProductData {
    my $self      = shift;
    my $productID = shift;

    my ( $isbn, $title, $author, $imprint, $publisher, $productType );

    if ( $productData{$productID} ) {
        $isbn        = $productData{$productID}{isbn};
        $title       = $productData{$productID}{title};
        $author      = $productData{$productID}{author};
        $imprint     = $productData{$productID}{imprint};
        $publisher   = $productData{$productID}{publisher};
        $productType = $productData{$productID}{productType};
    } else {
        my $bookProduct = BookPub::DB::Item::BookProduct->Lookup( product_id => $productID );

        # ISBN
        $isbn = $bookProduct->isbn13;
        if ( !$isbn ) {
            $isbn = $bookProduct->isbn10;
        }
        $productData{$productID}{isbn} = $isbn;

        # TITLE
        my $book = BookPub::DB::Item::Book->Lookup( book_id => $bookProduct->book_id );
        $title = $book->title;
        $productData{$productID}{title} = $title;

        # AUTHOR
        $author = BookPub::DB::Item::BookContributor->GetAuthorNamesByBookID( $bookProduct->book_id );
        my @author = split( ";", $author );
        $author = $author[0];
        $productData{$productID}{author} = $author;

        # IMPRINT
        $imprint = BookPub::DB::Item::Imprint->Lookup( imprint_id => $bookProduct->imprint_id );
        if ($imprint) {
            $imprint = $imprint->name;
            $productData{$productID}{imprint} = $imprint;
        }

        # PUBLISHER
        $publisher = BookPub::DB::Item::Publisher->Lookup( publisher_id => $bookProduct->publisher_id );
        if ($publisher) {
            $publisher = $publisher->name;
            $productData{$productID}{publisher} = $publisher;
        }

        # PRODUCT TYPE
        my $bookFormat = BookPub::DB::Item::BookFormat->Lookup( book_format_id => $bookProduct->book_format_id );
        my $bookFormatType = BookPub::DB::Item::BookFormatType->Lookup( book_format_type_id => $bookFormat->book_format_type_id );
        my $codeItem = BookPub::DB::Item::OnixCode->Lookup( onix_code_id => $bookFormatType->onix_code_id );
        $productType = $codeItem->description();
        $productData{$productID}{productType} = $productType;
    }

    return ( $isbn, $title, $author, $imprint, $publisher, $productType );
}

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

    my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->SUPER::_getReportingPeriod($fileID);

    # Apparently, we are not able to determine the reporting period from the sales
    # dates for some services.  Let's fall back to the seeing if we can get it from the filename.
    if ( !$reportingMonth && !$reportingQuarter ) {
        ( $reportingMonth, $reportingYear ) = $self->_getDateFromFilename($fileID);
    }

    return ( $reportingMonth, $reportingQuarter, $reportingYear );
}

# This is here to override the methon in the base module, Royalty.pm
sub _compareSalesMonths {
    my ( $self, $dateBeginNormal, $dateEndNormal ) = @_;

    my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->SUPER::_compareSalesMonths( $dateBeginNormal, $dateEndNormal );

    if ( !$reportingMonth && !$reportingQuarter ) {
        my $dateEndMonth = substr( $dateEndNormal, 5, 2 );

        $reportingMonth = $dateEndMonth;
        $reportingYear = substr( $dateEndNormal, 0, 4 );
    }

    return ( $reportingMonth, $reportingQuarter, $reportingYear );
}

sub _validateDate {
    my ( $self, $date ) = @_;

    my ( $year, $month, $day, $error );

    #Current Unix time
    my $currentTime = time();

    #Get the current year to check year ranges
    my ( $sec, $min, $hour, $mday, $mon, $currentYear, $wday, $yday, $isdst ) = localtime($currentTime);

    #Must add 1900 to current year to get 4 digit year value
    $currentYear += 1900;

    if ( $date =~ /^(\d{4})\-(\d{2})\-(\d{2})$/ ) {
        $year  = $1;
        $month = $2;
        $day   = $3;

        if ( ( $year < 1990 ) || ( $year > $currentYear ) ) {

            $error = "Date year $year out of range";

        } elsif ( ( $month == 0 ) || ( $month > 12 ) ) {

            $error = "Date month $month out of range";

        } else {

            my $daysInMonth = Days_in_Month( $year, $month );

            if ( ( $day < 1 ) || ( $day > $daysInMonth ) ) {
                $error = "Date day $day out of range";
            }
        }
    } else {
        $error = "'Date' in invalid format $date, need: yyyy-mm-dd";
    }

    return $error;
}

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 $sales     = BookPub::DB::Item::Sale->GetAllByFileID($fileID);
    my $file      = BookPub::DB::Item::File->Lookup( file_id => $fileID );
    my $fileName  = $file->orig_file_name;
    my $serviceID = $file->service_id;

    if (   $serviceID == BookPub::Tracker::Service::RSFORMAT()
        || $serviceID == BookPub::Tracker::Service::VITAL_SOURCE() ) {    # We need to grab the service id from a sale if
                                                                          # they are using the royaltyshare template or
                                                                          # or it's distributor-ish
        my $sale = BookPub::DB::Item::Sale->Lookup( file_id => $fileID );
        $serviceID = $sale->service_id;
    }

    while ( my $sale = $sales->next() ) {
        my ( $clientServiceID, %sanCriteria ) = $self->_getClientServiceID( $serviceID, $fileName, $sale );

        my ( $reportingMonth, $reportingQuarter, $reportingYear ) = $self->_getReportingPeriod($fileID);

        # If any of these are missing, we need to mark this as invalid.
        if ( ( !$reportingMonth && !$reportingQuarter ) || !$clientServiceID ) {
            $validation{isValid} = undef;

            if ( !$reportingMonth && !$reportingQuarter ) {
                $validation{"Reporting Period"} = "missing";
            }

            if ( !$clientServiceID ) {

                # For this one, it would helpful to pass along some additional info about the sale
                # and the criteria we're using to look for the SAN.
                foreach my $criteria ( sort keys %sanCriteria ) {
                    $validation{"SAN"}{$criteria} = $sanCriteria{$criteria};
                }
            }
        }
    }

    return %validation;
}

1;