#!/usr/bin/perl
# Royalty breakdown report
#
# ./royaltyBreakdown.pl -c clientID -s stmtID -r runID
#
# NOTE(S):
# For physical sales, the statement aggregates net unit information from sales as full
# numbers (no rounding), but rounds the net units before multiplying it by the net rate
# (% wholesale document terms) -- this is what gets stored in the income item record.
#
# In the sales report, the net units are displayed as full numbers and this is what
# is used for calculating the net earnings.  If you're trying to reconcile the net units
# and earnings for a statement line, you may see some slight differences due to rounding
# between the relevant sale(s) in the raw sales report and the statement line.
#

use strict;
use Getopt::Long;
use Data::Dumper;
use Time::HiRes qw ( time );
use Math::Round;
use Encode;
use File::Path qw(mkpath);
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );

use lib '/app/tools/common/lib';
use Common::RSApp;
use Common::RSMath;
use Common::CurrencyFormat;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::Payor;
use RPS::DB::Item::ArtistRoyaltyRun;
use RPS::DB::Item::ArtistRoyaltyStatement;
use RPS::DB::Item::ArtistRoyaltyAlbum;
use RPS::DB::Item::ArtistRoyaltyIncomeItem;
use RPS::DB::Item::Artist;
use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::Master;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::Region;
use RPS::DB::Item::Product;
use RPS::DB::Item::ClientOptions;
use RPS::File::Sale;
use RPS::Statement::Artist::PDF;

# Allow tuning of the verbosity of our output.
#
#my $gVerbosityLevel = 1;

my %contractMap;
my %albumMap;
my %productMap;
my %serviceNameMap; # hash of serviceID to name
my %trackMap;       # hash of id to name and artist

# Parse the command-line.
#
my %options;
parseCommandLine( \%options );

my $clientID = $options{clientID};
my $stmtID   = $options{stmtID};
my $_verbose = $options{verbose};
my $gRunID   = $options{runID};

my $appSingleton = Common::RSApp->new( clientID => $clientID );
my $dbo          = Common::RSApp::GetClientDB();
my $cdbo         = Common::RSApp::GetCommonDB();
my $dbh          = $dbo->DBH;

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

my $verbose = (scalar @$_verbose);
my $gVerbosityLevel = ($verbose > 0) ? $verbose : 1;

my $run        = RPS::DB::Item::ArtistRoyaltyRun->Lookup( artist_royalty_run_id => $gRunID );
my $payor      = RPS::DB::Item::Payor->Lookup( payor_id => $run->payor_id );
my $gPayorName = $payor->name;
my $gRunName   = $run->label;
my $gRunDate   = substr($run->date_created,0,10);

my $baseFilePath = RPS::Statement::Artist::PDF::StatementPathFromRunID($gRunID);
my $outFilePath  = $baseFilePath . "breakdown/";

if ( !-d $outFilePath ) {
    mkpath($outFilePath) or die "ERROR: Unable to create path $outFilePath: $!\n";
}

my @statements;
if ( $gRunID ) {
    my $sql = "SELECT artist_royalty_statement_id FROM artist_royalty_statement WHERE artist_royalty_run_id=$gRunID";
    my $sth = $dbo->DoCmd($sql);
    while( my( $id ) = $sth->fetchrow_array() ) {
        push @statements, $id;
    }
} else {
    push @statements, $stmtID;
}

my %incomeSourceMap;
my $sql = "SELECT income_source_id, name, description, format FROM income_source";
my $sth = $cdbo->DoCmd($sql); # RSCOMMON
while( my($id, $name, $description, $format) = $sth->fetchrow_array() ) {
    $incomeSourceMap{$id}{name}        = $name;
    $incomeSourceMap{$id}{description} = $description;
    $incomeSourceMap{$id}{format}      = $format;
}

my %regionMap;
$sql = "SELECT region_id, name FROM region";
$sth = $dbo->DoCmd($sql);
while( my($id, $name) = $sth->fetchrow_array() ) {
    $regionMap{$id} = $name;
}

my %rateTypeMap;
$sql = "SELECT contract_rate_type_id, name FROM contract_rate_type";
$sth = $cdbo->DoCmd($sql); # RSCOMMON
while( my($id, $name) = $sth->fetchrow_array() ) {
    $rateTypeMap{$id} = $name;
}

my %channelMap;
$sql = "SELECT channel_id, name FROM channel";
$sth = $dbo->DoCmd($sql);
while( my($id, $name) = $sth->fetchrow_array() ) {
    $channelMap{$id} = $name;
}

my %priceLevelMap;
$sql = "SELECT price_level_id, name FROM price_level";
$sth = $cdbo->DoCmd($sql); # RSCOMMON
while( my($id, $name) = $sth->fetchrow_array() ) {
    $priceLevelMap{$id} = $name;
}

# ------------------------------

# Generate the report(s) (one per statement)
foreach my $stmtID (@statements) {
    _processStatement( statement_id => $stmtID, verbose => $verbose );
}

# Now create a zip file of all of the reports
my $zip = Archive::Zip->new();
print "Creating zip file\n";

my $zipFile = $baseFilePath . "artist_run_" . $gRunID . "_royalty_breakdown.zip";

opendir(my $dh, $outFilePath) or die "Could not open directory '$outFilePath': $!";

while (my $filename = readdir($dh)) {
    # Skip '.' and '..' hidden directories
    next if $filename =~ /^\.\.?$/;
    my $fullPath = "$outFilePath/$filename";
    print "Adding: $filename\n";
    $zip->addFile($fullPath, $filename);
}
closedir($dh);

unless ( $zip->writeToFileNamed($zipFile) == AZ_OK ) {
    die "Error writing zip file: $!";
}

print "Zip file created: $zipFile\n";

# Clear the in-progress semaphore and add the completed one

my $generatingSemaphore = $baseFilePath . "ROYALTY_BREAKDOWN_GENERATING";
unlink($generatingSemaphore);

my $completeSemaphore = $baseFilePath . "ROYALTY_BREAKDOWN_COMPLETE";
open SEMAPHORE, "> $completeSemaphore";
print SEMAPHORE "done\n";
close SEMAPHORE;


# ------------------------------

sub _processStatement {
    my (%args) = @_;
    my $stmtID        = $args{statement_id};

    print "### Analyzing statementID $stmtID ...\n";
    my $statement = RPS::DB::Item::ArtistRoyaltyStatement->Lookup( artist_royalty_statement_id => $stmtID );
    die("ERROR: statement $stmtID not found !!!") if ( !$statement );

    my $runID     = $statement->artist_royalty_run_id;
    my $payeeID   = $statement->payee_id;

    my $payee         = RPS::DB::Item::ArtistPayee->Lookup( artist_payee_id => $payeeID );
    my $payeeName     = $payee->name;
    my $payeeClientID = $payee->client_account_id;

    my $fileName = _cleanName($payeeName) . "_" . $payeeID . "_" . _cleanName($gRunName) . "_" . $runID . "_" . $gRunDate . ".txt";
    my $targetFile = $outFilePath . $fileName;

    # Open file for writing
    open( my $outFile, ">" . $targetFile );
    binmode( $outFile, ':utf8' );

    # Add the header
    my @header = (
        'run-name',
        'payee',
        'contract-title',
        'catalog-no',
        'album-title',
        'album-artist',
        'product-title',
        'upc',
        'track-title',
        'track-artist',
        'isrc',
        'source',
        'source-name',
        'region',
        'country',
        'service-name',
        'channel',
        'price-tier',
        'net-units',
        'total (' . $currencyCode . ')',
        'item-id',
    );

    printLine( $outFile, join("\t", @header) );

    my $albumColl = RPS::DB::Item::ArtistRoyaltyAlbum->GetByArtistRoyaltyStatementID( $stmtID );

    print "Found " . $albumColl->size . " royalty album(s)\n";

    return if ( $albumColl->size == 0 );

    # To get the sales that fed into the statement, we need to get the set
    # of artist_royalty_album entries.  This will allow us to find the
    # income items, and from there we can get the sale_id's by looking in
    # sale_run_map.

    my @saleMap;

    # used for earnings by service report
    my $netUnits=0;
    my $netPrice=0;
    my $totalRevenue=0;

    while ( $albumColl->hasNext ) {
        my $royaltyAlbum = $albumColl->next;

        my $raID               = $royaltyAlbum->artist_royalty_album_id;
        my $raAlbumID          = $royaltyAlbum->album_id;
        my $raContractID       = $royaltyAlbum->artist_contract_id;
        my $raUnitLevelIncome  = $royaltyAlbum->unit_level_income;
        my $raNetRevenueIncome = $royaltyAlbum->net_revenue_income;

        print "Looking at raAlbumID $raID ...\n";
        my $aRef          = _getAlbumInfo($raAlbumID);
        my $albumTitle    = $aRef->{title};
        my $albumArtist   = $aRef->{artist};
        my $catalogNo     = $aRef->{catalog_number};

        my $contract      = _getContract($raContractID);
        my $contractTitle = $contract->title;

        my $itemColl = RPS::DB::Item::ArtistRoyaltyIncomeItem->GetByArtistRoyaltyAlbumID( $raID );

        # Find the sale(s) that were aggregated into each statement line
        #
        my @itemLines;
        while ( $itemColl->hasNext ) {
            my $item      = $itemColl->next;
            my %itemRows; # We'll use this to consolidate item sales with the same country and service

            my $itemID       = $item->artist_royalty_income_item_id;
            my $itemTrackID  = $item->track_id;
            my $itemTotal    = $item->total;
            my $usesDefaultNetRate = $item->uses_default_net_rate;

            my $rateTypeID  = $item->contract_rate_type_id;
            my $netRate     = $item->net_rate;
            my $reserveRate = $contract->reserve_rate;
            my $freeGoods   = $item->free_goods_deduction / 100;
            my $packaging   = $item->packaging_deduction / 100;

            my $rateType    = $rateTypeMap{$rateTypeID};

            my $incomeSourceID = $item->income_source_id;
            my $incomeSource = $incomeSourceMap{$incomeSourceID}{name};
            my $incomeSourceDescription = $incomeSourceMap{$incomeSourceID}{description};
            my $incomeSourceFormat = $incomeSourceMap{$incomeSourceID}{format};

            my $regionID = $item->region_id;
            my $region   = (defined $regionID) ? $regionMap{$regionID} : 'All';

            my $channel     = $channelMap{$item->channel_id} || '';
            my $priceLevel  = $priceLevelMap{$item->price_level_id};

            my $revenueLiquidated = $item->revenue_liquidated;
            my $revenueReserved   = $item->revenue_reserved;
            my $unitsReserved     = $item->units_reserved;

            my $unitReserveRate;
            if ( $item->sales ) {
                $unitReserveRate  = $unitsReserved / $item->sales;
            }

            my $trackTitle;
            my $trackArtist;
            my $isrc;
            if ( $itemTrackID ) {
                my $tRef = _getTrackInfo($itemTrackID);
                $trackTitle  = $tRef->{title};
                $trackArtist = $tRef->{artist};
                $isrc        = $tRef->{isrc};
            }

            #print "  Looking at itemID $itemID  usesDefaultNetRate($usesDefaultNetRate) ... ";
            print "  [$itemID] $incomeSource rg($region) ch($channel) pl($priceLevel) type($rateType) ...\n";

            # We are going to use the consolidated physical sales data for some versions of this report.
            # We'll check for the feature flag and use that in combination with the income source format below.

            my $ff = RPS::DB::Item::ClientOptions->Get( 'royalty_breakdown' );
            # Value of 1 = Consolidate physical lines (use income item)
            # Value of 2 = Break out physical lines into sales 

            if ( $incomeSourceFormat == RPS::DB::Item::IncomeSource::kFormatPhysical && $ff == 1 ) {

                my @columns;

                push @columns, $gRunName; # run-name
                push @columns, $payeeName; # payee
                push @columns, $contractTitle; # contract-title
                push @columns, $catalogNo; # catalog-no
                push @columns, $albumTitle; # album-title
                push @columns, $albumArtist; # album-artist
                push @columns, '-'; # product-title
                push @columns, '-'; # upc
                push @columns, $trackTitle; # track-title
                push @columns, $trackArtist; # track-artist
                push @columns, $isrc; # isrc
                push @columns, $incomeSource; # source
                push @columns, $incomeSourceDescription; # source-name
                push @columns, $region; # region
                push @columns, '-'; # country
                push @columns, 'Physical Sales'; # service-name
                push @columns, $channel; # channel
                push @columns, $priceLevel; # price-tier
                push @columns, $item->net_units; # net-units
                push @columns, $item->total; # total (CUR)
                push @columns, $itemID; # artist_royalty_income_item_id

                printLine( $outFile, join("\t", @columns) );

            } else {

                my $sql = "SELECT DISTINCT sale_id FROM sale_run_map WHERE run_type='artr' "
                    . "AND run_id=$runID AND statement_item_id=$itemID AND status='paid'";
    #            $sql .= " LIMIT 1"; # XXX XXX XXX XXX
                my $sth = $dbo->DoCmd($sql);
                my $nSales = $sth->rows;

                print "found $nSales sale(s)\n";

                # When scanning the sales that feed into an income item, for digital net revenue sales we'll
                # calculate the net earnings on the individual sales and aggregate as we go along.  For unit-based
                # physical, we can't easily calculate the individual net earnings on each sale due to reserves
                # and any associated rounding (e.g., we lose precision when calculating the earnings per sale
                # and then aggregating the earnings only exacerbates the situation).  To account for the this,
                # we still round the reserve units per sale and then calculate an initial net earnings for that
                # sale.  However, the final net earnings for that sale must be adjusted in relation to the other
                # sales feeding info the same income item.
                #
                # For example, with Cleo's income item 32814824, the income item total is 20.25.  There are 27
                # sales feeding into that item, and the net earnings for each sale is 1.0125.  If we sum those up
                # we end up with 27.3375, which does not equal the item total.
                # Now, if we divide the item total by the sum of those net earnings, we can use that ratio to
                # adjust the individual net earnings such that the sum will indeed match the item total:
                #
                #  20.25 / 27.3375 = .7407 (ratio)
                #  1.0125 * .7407 = 0.75 (adjusted net earnings)
                #
                # The adjusted net earnings for each sale is then 0.75, and 27 * 0.75 = 20.25.
                #
                # Hence, for unit-based calculations we need to process the income item sales in two passes:
                #  1) Calculate the net earnings for each sale feeding into the income item, and keep a running
                #     total of the sum of those net earnings (salesRevenue).  Save the sale information along
                #     with the net units and earnings into the 'saleArray' array.  Unlike net revenue sales,
                #     do NOT aggregate the sales into our custom report hashes;
                #
                #  2) Using the net earnings sum from the first pass:
                #     - Calculate an adjustment by dividing the income item total by the sum of the net earnings.
                #     - For each sale that we saved in the 1st pass, adjust the net earning of the sale by
                #       multiplying the sale's net earning (calculated in the 1st pass) by the adjustment ratio.
                #     - Aggregate the sale into our custom report hashes using the adjusted net earnings data.
                #

                # @saleArray is used for physical sale processing w/ unit-based royalties:
                #   $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale
                #   $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail
                #   $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale
                #   $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail
                #   $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed
                #
                my @saleArray;

                # $salesRevenue holds the sum of the physical item sales unadjusted net earnings (e.g., this value
                # might equal the income item total, however due to rounding issues it may be higher than the
                # income item total).
                #
                my $salesRevenue;

                my $productTitle;
                my $upc;

                while( my($saleID) = $sth->fetchrow_array() ) {


                    my $sql = "SELECT file_id, IFNULL(sale.service_id, file.service_id) AS service_id, "
                        . "product_type, format_type, "
                        . "sale.units, price, conversion_rate, sale.currency_code, country_code, product_id, "
                        . "wholesale_price, retail_price, "
                        . "sales, sales_revenue, returns, returns_revenue, sale.gross_revenue FROM sale "
                        . "LEFT JOIN file USING (file_id) WHERE sale_id=$saleID";
                    my $sth = $dbo->DoCmd($sql);

                    my @sArray = $sth->fetchrow_array();
                    my( $fileID, $serviceID, $pType, $formatType,
                        $units, $price, $conversionRate, $currencyCode, $countryCode, $productID,
                        $wholesalePrice, $retailPrice,
                        $sales, $sRevenue, $returns, $rRevenue, $saleGrossRevenue ) = @sArray;

                    _report("$saleID : s $serviceID u $units price $price  -- sales $sales ($sRevenue)  returns $returns ($rRevenue)", 2) if ( $verbose );

                    my $pRef      = _getProductInfo($productID);
                    $productTitle = $pRef->{title};
                    $upc          = $pRef->{upc};

                    # FYI:
                    # dbmaster\RSCOMMON>select * from contract_rate_type;
                    # +-----------------------+----------------------+--------------------------------------+-------+---------------+
                    # | contract_rate_type_id | name                 | description                          | fixed | display_order |
                    # +-----------------------+----------------------+--------------------------------------+-------+---------------+
                    # |                     1 | % Retail             | Percent of retail price              |     0 |             1 |
                    # |                     2 | % Wholesale          | Percent of wholesale price           |     0 |             2 |
                    # |                     3 | % PPD                | Percent of published price to dealer |     0 |             3 |
                    # |                     4 | % Avg                | Percent of average price             |     0 |             4 |
                    # |                     5 | % Net Revenue        | Percent of net revenue               |     0 |             6 |
                    # |                     6 | Fixed                | Fixed price per unit                 |     1 |             7 |
                    # |                     7 | % Retail Document    | Percent of retail document price     |     0 |             8 |
                    # |                     8 | % Wholesale Document | Percent of wholesale document price  |     0 |             9 |
                    # |                     9 | Non-payable          | Not eligible for payment             |     0 |            10 |
                    # |                    10 | % Gross Revenue      | Percent of gross revenue             |     0 |             5 |
                    # +-----------------------+----------------------+--------------------------------------+-------+---------------+
                    # 10 rows in set (0.00 sec)
                    #
                    # use constant kRateTypePercentRetail            => 1;
                    # use constant kRateTypePercentWholesale         => 2;
                    # use constant kRateTypePercentPPD               => 3; DEPRECATED
                    # use constant kRateTypePercentAverage           => 4; DEPRECATED
                    # use constant kRateTypePercentRevenue           => 5;
                    # use constant kRateTypeFixed                    => 6;
                    # use constant kRateTypePercentDocumentRetail    => 7;
                    # use constant kRateTypePercentDocumentWholesale => 8;
                    # use constant kRateTypeNonPayable               => 9;
                    # use constant kRateTypePercentGrossRevenue      => 10;
                    #

                    # Calculate revenue for this sale...
                    #
                    my $_units; # gross units
                    my $_revenue;

                    my $reserved;        # for unit reserves
                    my $reserveRevenue;  # for net rev reserves

                    my $netUnits;
                    my $grossRevenue;
                    my $netEarnings;

                    if ( $pType =~ /(A|T)/ ) {  # digital sales

                        if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeNonPayable ) {
                            print STDERR "detected digital sale with nonpayable rateTypeID $rateTypeID -- skipping !!!\n"; # XXX
                            next;
                        }

                        $netUnits = $units;

                        if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) {
                            $grossRevenue = $saleGrossRevenue  * $conversionRate;
                        } else {
                            $grossRevenue   = $units * $price * $conversionRate;
                        }

                        $netEarnings = $grossRevenue;

                        # adjust _revenue to reflect the applicable statement item
                        $netEarnings *= ( $netRate / 100 );

                    } else {  # physical sales

                        # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX
                        #
                        # 9/16/21 - For physical sales in a unit-based income item, we need to defer
                        # the final revenue calculation for each sale until we've seen all physical
                        # sales that will apply to the income item.
                        #
                        # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX

                        my $needToAdjustRevenue; # FLAG: if set, use 2nd pass to adjust revenue before custom aggregation

                        $_units       = $sales - $returns;
                        $netUnits     = $_units;

                        if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) {
                            $grossRevenue = $saleGrossRevenue  * $conversionRate;
                        } else {
                            $grossRevenue = ($sRevenue - $rRevenue) * $conversionRate;
                        }

                        # The net earnings will be unit or revenue-based, and has to
                        # be calculated for the sale based on the information in the
                        # artist_royalty_income_item that the sale aggregated into.
                        #
                        if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed ) {

                            # XXX unit-based royalties XXX

                            # For these, the net rate has the price, percentage of sales, pkg deduction.
                            # already factored in.

                            # Take the gross units and substract reserves
                            $netUnits = $sales;

                            if ( $sales > 0 ) {
                                # The reserved units are calculated per sale and rounded here.  This is
                                # different than the statement income item -- on the statement, we aggregate
                                # the sales into the income item and then calculate reserve units.  Because
                                # this report is aggregating sales on a custom basis, we need to calculate
                                # reserves per sale.  However, by calculating individual sale reserves you
                                # can run into issues when trying to manually verify the income item totals
                                # using the revenue information shown on a raw sales report.  We get around
                                # this by adjusting each sale's revenue information using a two-pass approach
                                # as outlined above.
                                #
                                $reserved = $unitReserveRate * $sales;
                                $reserved = Common::RSMath::round( $reserved, 0 );
                                $netUnits -= $reserved;
                            }

                            $netUnits -= $returns;
                            $netEarnings  = $netUnits * $netRate;
                            $needToAdjustRevenue = 1;

                        } elsif ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue ||
                                $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue ) {

                            # take reserves off revenue
                            $reserveRevenue = ($reserveRate/100) * $grossRevenue;

                            $netEarnings = $grossRevenue - $reserveRevenue;
                            $netEarnings *= ( $netRate / 100 );

                            # Need to account for liquidations, so defer the aggregation to the 2nd pass
                            $needToAdjustRevenue = 1;

                        } elsif ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeNonPayable ) {
                            print STDERR "detected physical sale with nonpayable rateTypeID $rateTypeID -- skipping !!!\n"; # XXX
                            next;
                        } else {
                            die("detected physical sale with unexpected rateTypeID $rateTypeID !!!"); # XXX TODO
                        }


                        if ( $needToAdjustRevenue ) {
                            $salesRevenue += $netEarnings;

                            push @sArray, $saleID;
                            push @sArray, $netUnits;
                            push @sArray, $netEarnings;
                            push @sArray, $grossRevenue;
                            push @sArray, $_units; # gross units
                            push @sArray, $reserved; # units reserved
                            push @sArray, $reserveRevenue;

                            push @saleArray, \@sArray;

                            next; # 9/16/21 - skip (defer) sale reporting and aggregation to 2nd pass below
                        }
                    }

                    $itemRows{$serviceID}{$countryCode}{net_units} += $netUnits;
                    $itemRows{$serviceID}{$countryCode}{net_earnings} += $netEarnings;

                } # sale loop

                if ( @saleArray ) {  # process physical sales for unit-based royalties ...
                    print "Processing physical sales for unit-based royalties ...\n";

                    # For unit-based calculations

                    # if no sales revenue then we don't need to be adjusting anything
                    my $coeff = ($salesRevenue) ? ($itemTotal / $salesRevenue) : 1;

                    # For revenue-based calculations
                    my $netRevLiquidated = $revenueLiquidated / (scalar @saleArray);
                    my $netRevReserved   = $revenueReserved / (scalar @saleArray);

                    if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale ||
                        $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail || 
                        $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale ||
                        $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail ||
                        $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed
                    ) {

                        print "PASS2 (unit-based): itemID $itemID itemTotal($itemTotal) / salesRevenue($salesRevenue) = coeef($coeff)\n"; # XXX

                    } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue ||
                        $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue
                    ) {

                        my $nSales = scalar @saleArray;
                        print "PASS2 (revenue-based): itemID $itemID revenueLiquidated($revenueLiquidated) / "
                            . "nSales($nSales) = netRevLiquidated($netRevLiquidated)\n"; # XXX

                    }

                    foreach my $sArray (@saleArray) {

                        my( $fileID, $serviceID, $pType, $formatType,
                            $units, $price, $conversionRate, $currencyCode, $countryCode, $productID,
                            $wholesalePrice, $retailPrice,
                            $sales, $sRevenue, $returns, $rRevenue, $saleGrossRevenue,
                            $saleID, $netUnits, $_netEarnings, $grossRevenue, $_units, $reserved, $reserveRevenue ) = @$sArray;

                        #print "PASS2: D: sArray = ". Dumper(@$sArray) . "\n";

                        my $netEarnings;

                        if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail  ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed
                        ) {

                            $netEarnings = $_netEarnings * $coeff;  # adjust based on sum of all sales net earnings w.r.t. item total

                        } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue ||
                            $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentGrossRevenue
                        ) {

                            $netEarnings = $grossRevenue - $netRevReserved + $netRevLiquidated;
                            $netEarnings *= ( $netRate / 100 );

                            # Now apply deductions
                            $netEarnings *= (1 - $freeGoods) * (1 - $packaging);
                        }

    #                    _report("  PASS2: $saleID : netEarnings($_netEarnings) -> adjEarnings($netEarnings) "
    #                        . "s $serviceID u $units price $price  -- sales $sales ($sRevenue)  returns $returns ($rRevenue)");


                        $itemRows{$serviceID}{$countryCode}{net_units} += $netUnits;
                        $itemRows{$serviceID}{$countryCode}{net_earnings} += $netEarnings;

                    } # physical saleArray loop

                } # 2nd pass for physical unit-based royalties

                # Print out the rows for this item.
                foreach my $serviceID ( keys %itemRows ) {
                    my $serviceRows = $itemRows{$serviceID};
                    my $serviceName = _getServiceName( $serviceID );

                    foreach my $countryCode ( keys %$serviceRows ) {
                        my $countryRows = $serviceRows->{$countryCode};
                        my @columns;

                        push @columns, $gRunName; # run-name
                        push @columns, $payeeName; # payee
                        push @columns, $contractTitle; # contract-title
                        push @columns, $catalogNo; # catalog-no
                        push @columns, $albumTitle; # album-title
                        push @columns, $albumArtist; # album-artist
                        push @columns, $productTitle; # product-title
                        push @columns, $upc; # upc
                        push @columns, $trackTitle; # track-title
                        push @columns, $trackArtist; # track-artist
                        push @columns, $isrc; # isrc
                        push @columns, $incomeSource; # source
                        push @columns, $incomeSourceDescription; # source-name
                        push @columns, $region; # region
                        push @columns, $countryCode; # country
                        push @columns, $serviceName; # service-name
                        push @columns, $channel; # channel
                        push @columns, $priceLevel; # price-tier
                        push @columns, $countryRows->{net_units}; # net-units
                        push @columns, $countryRows->{net_earnings}; # total (CUR)
                        push @columns, $itemID; # artist_royalty_income_item_id

                        printLine( $outFile, join("\t", @columns) );
                    }
                }

                undef @saleArray;
                undef $salesRevenue;

            }
        } # artist_royalty_income_item loop

        print "\n";

        undef $itemColl;

    }# artist_royalty_album loop

    # All done, close it up.
    #
    close $outFile;    
} # _processStatement

#
# Boring script stuff below...
#

sub _getServiceName {
    my $serviceID = shift;
#    print "D: _getServiceName serviceID = $serviceID\n";
    if ( !exists $serviceNameMap{$serviceID} ) {
        my $sql = "SELECT service_name FROM service WHERE service_id=$serviceID";
        my $sth = $dbo->DoCmd($sql);
        my( $serviceName ) = $sth->fetchrow_array();
        $serviceNameMap{$serviceID} = $serviceName;
    }
    return $serviceNameMap{$serviceID};
}

sub parseCommandLine {
    my ($a) = @_;

    my $clientID;
    my $stmtID;
    my $runID;

    my @verbose;

    if ( ! GetOptions(
        'c=i'         => \$clientID,
        's|stmt=i'    => \$stmtID,
        'r|run=i'     => \$runID,
        'verbose'     => \@verbose,
    )) {
        die("An error has occurred while parsing arguments, aborting\n");
    }

    $a->{clientID}   = $clientID;
    $a->{stmtID}     = $stmtID;
    $a->{runID}      = $runID;
    $a->{verbose}    = \@verbose;
}

sub usage {
    print STDERR "\nusage: $0 -c clientID -s stmtID -r runID\n";
}

sub _report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}

sub _getAlbumInfo { # returns hash with album info
    my $albumID = shift;
    if ( !exists $albumMap{$albumID} ) {
        my $album = RPS::DB::Item::Album->Lookup( album_id => $albumID );
        my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $album->artist_id );
        $albumMap{$albumID}{title}          = $album->title;
        $albumMap{$albumID}{catalog_number} = $album->catalog_number;
        $albumMap{$albumID}{artist}         = $artist->name;
    }
    return $albumMap{$albumID};
}

sub _getTrackInfo { # returns hash with track info
    my $trackID = shift;
    if ( !exists $trackMap{$trackID} ) {
        my $track = RPS::DB::Item::Track->Lookup( track_id => $trackID );
        die("invalid trackID $trackID  !!!") if ( !$track );
        my $master = RPS::DB::Item::Master->Lookup( master_id => $track->master_id );
        my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $track->artist_id );
        $trackMap{$trackID}{title}  = $track->title;
        $trackMap{$trackID}{artist} = $artist->name;
        $trackMap{$trackID}{isrc}   = $master->isrc;
    }
    return $trackMap{$trackID};
}

sub _getProductInfo { # returns hash with product info
    my $productID = shift;  
    if ( !exists $productMap{$productID} ) {
        my $product = RPS::DB::Item::Product->Lookup( product_id => $productID );

        # If this is a track product, we want to pull this data from the parent product
        my $parentProductID = $product->parent_product_id;
        if ( $parentProductID ) {
            $product = RPS::DB::Item::Product->Lookup( product_id => $parentProductID );
        }

        $productMap{$productID}{title} = $product->title;
        $productMap{$productID}{upc} = $product->upc_ean;
    }   
    return $productMap{$productID};
} 

sub _getContract {
    my $contractID = shift;
    if ( !exists $contractMap{$contractID} ) {
        my $contract = RPS::DB::Item::NewArtistContract->Lookup( artist_contract_id => $contractID );
        $contractMap{$contractID} = $contract;
    }
    return $contractMap{$contractID};
}

sub _cleanName {
    my ($name) = @_;
    # Replace '&' with 'and' - the normal clean function doesn't do that.
    #
    $name =~ s/ \& / and /g;
    $name = Common::Util::clean_name($name);
    return $name;
}

sub printLine {
    my ( $outFile, $line ) = @_;
    print $outFile encode( 'ascii', $line, sub { ' ' } ) . "\r\n";
}