#!/usr/bin/perl
#------------------------------------------------------------ 
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------ 
use strict;

use Data::Dumper;
use Getopt::Std;
use POSIX qw(ceil);
use POSIX qw(:sys_wait_h :signal_h :errno_h);
use Sys::Hostname;
use Carp;

use IO::Handle;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::RSApp;
use Common::RSMath;
use Common::TextProgressBar;
use Common::Assert;
use Common::DB::Item::Client;


use lib '/app/tools/raptor/lib';
use Raptor::DB::Item::Sale;
use Raptor::DB::Item::File;


use lib '/app/tools/rps/lib';
use RPS::ArtistRoyalty::Utils;
use RPS::Statement::Artist::PDF;
use RPS::RoyaltyRun::Status;
use RPS::File::Sale;

use RPS::DB::Item::Payor;
#use RPS::DB::Item::SaleArtistRoyaltyRunMap;
use RPS::DB::Item::SaleRunMap;
use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::Artist;
use RPS::DB::Item::ArtistPayeeAccount;
use RPS::DB::Item::ArtistRoyaltyRun;
use RPS::DB::Item::ArtistRoyaltyAlbum;
use RPS::DB::Item::ArtistRoyaltyAlbumBalanceAccount;
use RPS::DB::Item::ArtistRoyaltyStatement;
use RPS::DB::Item::ArtistRoyaltyIncomeItem;
use RPS::DB::Item::ArtistRoyaltyExpenseItem;
use RPS::DB::Item::ArtistRoyaltyRunMissedSaleLog;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::ArtistContractTermReserve;
use RPS::DB::Item::ArtistContractTermReserveRun;
use RPS::DB::Item::ReserveLiquidation;
use RPS::DB::Item::Channel;
use RPS::DB::Item::Expense;
use RPS::DB::Item::Format;
use RPS::DB::Item::Product;
use RPS::DB::Item::Region;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;
use RPS::DB::Item::RegionCountryMap;
use RPS::DB::Item::ServiceFormatChannelMap;
use RPS::DB::Item::TrackContract;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::ProductPrice;
use RPS::DB::Item::Price;
use RPS::DB::Item::PriceLevel;
use RPS::DB::Item::ExpenseType;
use RPS::DB::Item::ExpenseName;
use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::Service;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::Master;
use RPS::DB::Item::StatRate;
use RPS::DB::Item::ProductTrack;
use RPS::DB::Item::PendingTransaction;
use RPS::DB::Item::ArtistRoyaltyTransaction;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::SaleProductType;
use RPS::DB::Item::DistributionFee;
use RPS::DB::Item::LicenseIncome;
use RPS::DB::Item::ArtistContractLicenseIncome;
use RPS::DB::Item::ArtistRoyaltyLicenseIncomeItem;
use RPS::DB::Item::ContractLevelLicenseIncomeBalanceAccount;

use RPS::ArtistRoyalty::Job::CreateReservePipelineReport;

# This constant tunes how often we spit out a 'processed X sales' message
#
use constant kSaleProgressQuanta => 20;


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


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




# JPK - This is not my usual style, but...
# Set up some global data structures that we'll fill in as we process sales.
#
my %gData;
my %gCrossed;


# Get the hostname we are running on.
#
my $hostname = hostname();

my $gRunID = $options{runID};


# Now, we're going to fork off a child process.
# This way I can tell (and therefore log) whether we exited cleanly or because
# of some exception
#

my $gChildPID = fork();
if ($gChildPID)
{
    _parent();
}
else
{
    _child();
}


sub _child
{
    # Install a signal handler to catch ctrl-c.
    #
    $SIG{INT} = \&CTRL_C;

    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new(clientID => $options{clientID});


    # There should already be an entry in the run table, with a status of kWaitingToRun.
    #
    my $obj = RPS::DB::Item::ArtistRoyaltyRun->Lookup
    (
        artist_royalty_run_id => $gRunID,
    );
    if (! $obj)
    {
        die "ERROR: run_id $gRunID is not valid\n";
    }


    # Sanity check - make sure this run is in the correct state.
    #
    if (RPS::RoyaltyRun::Status::kWaitingToRun != $obj->status())
    {
        die "ERROR: run_id $gRunID is not in the 'waiting to run' state\n";
    }

    
    # Set the start time and pid fields
    #
    $obj->start_time(Common::DB::Item::kDateTimeNow);
    $obj->pid($$);
    $obj->status(RPS::RoyaltyRun::Status::kRunning);

    $obj->save();
    

    my $payors = [ $obj->payor_id ];

#    # Get the list of payors (creating if necessary)
#    #
#    my $payors = $options{payors};
#    if (! $payors)
#    {
#        $payors = _getAllPayorIDs();
#    }

    # Now... do the run!
    #
    _report("*** Artist Royalty Run $gRunID for client id " . $options{clientID} . " for period " . $options{label});
    _run_royalties($payors);


    # Save off the processed sales map for later perusal.
    #
#    foreach my $saleID (keys %gProcessedSales)
#    {
#        my $entry = RPS::DB::Item::SaleArtistRoyaltyRunMap->Create
#        (
#        sale_id => $saleID,
#        artist_royalty_run_id => $gRunID,
#        );
#
#        $entry->save();
#    }



    # Go forth and create pdf files, I Command You!
    # !!! This is using a lot of CPU...  Let's nice the process.
    #
    my $statementsPath = RPS::Statement::Artist::PDF::StatementPathFromRunID($gRunID, $options{clientID});

    my $cmd = "/app/tools/rps/bin/statements/createAllArtistStatementsPDF.pl -c " . $options{clientID} . " -r $gRunID -p $statementsPath";
    system($cmd);
 
 
 
    # Create the pipeline report
    #
#    _createArtistReservePipelineReport(); 
 
    # Go forth and create the artist reserve pipeline report, I Command You!
    #
    # Create the new job, and put it in the queue.
    #
    _report("*** adding reserve pipeline job to queue");
    my $jobArgs = RPS::ArtistRoyalty::Job::CreateReservePipelineReport->new
    (
        runID 		=> $gRunID,
        clientID 	=> $options{clientID},
    );
    my $job = $jobArgs->enqueue(); 
    _report("*** done adding reserve pipeline job to queue!");   

    
    
}


my $gChildExitCode;
my $gChildIsRunning;
sub _parent
{
    # Install a signal handler to catch the child's exit status.
    # 
    local $SIG{CHLD} = \&REAPER;

    # Ctrl-c catching is rather weird with forked processes.
    # I want to ignore this signal in the parent, so that the
    # child catches it.
    # Using 'local' so that only the parent ignores the signal.
    #
    local $SIG{INT} = 'IGNORE';


    my $runID = $options{runID};

    # Wait for the child process to complete
    #
    $gChildIsRunning = 1;
    while ($gChildIsRunning)
    {
        sleep(1);
    }

    my $status;
    if (0 == $gChildExitCode)
    {
        $status = RPS::RoyaltyRun::Status::kComplete;
    }
    elsif (1 == $gChildExitCode)
    {
        $status = RPS::RoyaltyRun::Status::kAborted;
    }
    else
    {
        $status = RPS::RoyaltyRun::Status::kError;
    }
    

    # Mark the run as complete
    #
    my $appSingleton = Common::RSApp->new(clientID => $options{clientID});
    my $obj = RPS::DB::Item::ArtistRoyaltyRun->Lookup(artist_royalty_run_id => $runID);
    $obj->status($status);
    $obj->end_time(Common::DB::Item::kDateTimeNow);
    $obj->save();

}


sub REAPER
{
    my $deadChildPID = waitpid(-1, &WNOHANG);

    if (-1 == $deadChildPID)
    {
        # no child - ignore this
    }
    elsif (WIFEXITED($?))
    {
        # The child really exited.
        # Let's find out why.
        #
        $gChildExitCode = $? >> 8;
        $gChildIsRunning = 0;
    }
    $SIG{CHLD} = \&REAPER;
}


sub CTRL_C
{
    _report("caught a ctrl-c, aborting");
    exit(1);
}


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


sub _run_royalties
{
    my ($payors) = @_;

    _report("Building the cross-collateralization lookup table...");
    _buildCrossTable();

    # 
    # ---  Process Sales 
    #
    _processSales($payors);
    _processLicenseIncomeSales($payors);

    _processReserves($payors);
    _fillInData();

#print Dumper(\%gCrossed) . "\n";


    #
    # --- Process Expenses
    #
    _processExpenses($payors);


    #
    # --- Generate the statements.
    #
    _generateStatements($payors);


}


sub _generateStatements
{
    my ($payorList) = @_;

    my %payorIDMap;
    foreach my $payorID (@$payorList)
    {
        $payorIDMap{$payorID} = 1;
    }


    _report("--- generating statements");
    # _report("data: " . Dumper(\%gData), 4);

    my $progressBar = Common::TextProgressBar->new(scalar (keys %gData), kSaleProgressQuanta);
    if ($gProgress)
    {
        $| = 1;
        $progressBar->display(\*STDOUT);
    }


    # Fetch the current balances for _all_ artist payees, then add blank entries to the main
    # data structure for those artists that aren't currently in there.
    # This will result in our producing statements for payees that have balances but no sales.
    # (which is what we want).
    #
    my $payeePreviousBalanceHash = _getArtistPayeeBalances();
    foreach my $payorID (keys %$payeePreviousBalanceHash)
    {
        next unless ($payorIDMap{$payorID});

        my $artistPayeeIDHash = $payeePreviousBalanceHash->{$payorID};
        foreach my $artistPayeeID (keys %$artistPayeeIDHash)
        {
            if (! exists $gData{$payorID}{$artistPayeeID})
            {
                $gData{$payorID}{$artistPayeeID} = {};
            }
        }
    }


    # We need to also fetch any _album balances_, so we can generate statements for those as well.
    #
    my $albumBalanceHash = _getAlbumBalances();

    foreach my $payorID (keys %$albumBalanceHash)
    {
        next unless ($payorIDMap{$payorID});

        my $artistPayeeIDHash = $albumBalanceHash->{$payorID};
        foreach my $artistPayeeID (keys %$artistPayeeIDHash)
        {
            my $albumIDHash = $artistPayeeIDHash->{$artistPayeeID};
            foreach my $albumID (keys %$albumIDHash)
            {
                my $artistContractIDHash = $albumIDHash->{$albumID};
                foreach my $artistContractID (keys %$artistContractIDHash)
                {
                    if (! exists $gData{$payorID}{$artistPayeeID}{$artistContractID}{$albumID})
                    {
                        $gData{$payorID}{$artistPayeeID}{$artistContractID}{$albumID} = {};
                    }
                }
            }
        }
    }


    foreach my $payorID (keys %gData)
    {
        # !!! This is probably not necessary, but, hey, what the hell
        #
        next unless ($payorIDMap{$payorID});
    my $artistPayeeHash = $gData{$payorID};
    foreach my $artistPayeeID (keys %$artistPayeeHash)
#    foreach my $artistPayeeID (keys %gData)
    {
        $progressBar->increment();
        if ($gProgress)
        {
            $| = 1;
            $progressBar->display(\*STDOUT);
        }

        my $artistPayeeData = RPS::DB::Item::ArtistPayee->Lookup(artist_payee_id => $artistPayeeID);

        if (RPS::DB::Item::ArtistPayee::kStatusInactive == $artistPayeeData->status)
        {
            _report("artist payee $artistPayeeID is inactive - skipping", 3);
#            _logMissedSale($sale, "Artist payee $artistPayeeID is inactive");
            next;
        }
        
        # Get the payee account info
        #
        my $artistPayeeAccount = RPS::DB::Item::ArtistPayeeAccount->Lookup(artist_payee_id => $artistPayeeID, payor_id => $payorID);
        my $minPayment = 0;
        if ($artistPayeeAccount)
        {
            $minPayment = $artistPayeeAccount->min_payment;
        }


        # Create a statement for this payee.
        # We'll have to save it twice - once to get the
        # id (which the other tables will need), and once
        # after all is done.
        #
        my $statement = RPS::DB::Item::ArtistRoyaltyStatement->Create
        (
            payee_id => $artistPayeeID,
            payor_id => $payorID,
            artist_royalty_run_id => $gRunID,
        );
        $statement->save();
        my $statementID = $statement->artist_royalty_statement_id;
        my $statementLicenseIncomeSubtotal = 0;

        my $contractLevelLicenseIncomeSubtotal = 0;
        # !!! need previous balance, and total (which includes the previous balance)
        #

        my $statementSubtotal = 0;
        my $crossIncome = 0;
        my $crossExpense = 0;
        my $crossBalance = 0;
        my $crossTotal = 0;
        my $payeePreviousBalance = $payeePreviousBalanceHash->{$payorID}{$artistPayeeID}{balance};
        my $payeePendingTransactions = $payeePreviousBalanceHash->{$payorID}{$artistPayeeID}{pending};


        my $contractIDHash = $artistPayeeHash->{$artistPayeeID};
#        my $contractIDHash = $gData{$artistPayeeID};
        foreach my $contractID (keys %$contractIDHash)
        {
            # Fetch the default term, so we can tell later on when we're
            # dealing with items in the default rate pool.
            #
            my $defaultTerm = RPS::DB::Item::NewArtistContractTerm->GetDefault($contractID);
            my $defaultTermID = 0;
            my $defaultNetRevenueRate = 100;
            if ($defaultTerm)
            {
                $defaultTermID = $defaultTerm->artist_contract_term_id;
                $defaultNetRevenueRate = $defaultTerm->rate;
            }

            my $albumIDHash = $contractIDHash->{$contractID};
            foreach my $albumID (keys %$albumIDHash)
            {
                # Create the 'album' grouping table item.
                #
                my $albumItem = RPS::DB::Item::ArtistRoyaltyAlbum->Create
                (
                    artist_royalty_statement_id => $statementID,
                    album_id => $albumID,
                    artist_contract_id => $contractID,
                    default_net_revenue_rate => $defaultNetRevenueRate,
                );

                # !!! Don't save this if the album_id == 0...
                #
                if ($albumID > 0)
                {
                    $albumItem->save();
                }

                my $albumItemID = $albumItem->artist_royalty_album_id;
                $albumItemID = 0 unless $albumItemID;

                my $albumUnitLevelIncome = 0;
                my $albumNetRevenueIncome = 0;
                my $albumNetRevenueExpenses = 0;
                my $albumRecoupableExpenses = 0;
                my $albumLicenseIncomeSubtotal = 0;


                my $trackIDHash = $albumIDHash->{$albumID};
                foreach my $trackID (%$trackIDHash)
                {
                    my $productIDHash = $trackIDHash->{$trackID};
                    foreach my $productID (keys %$productIDHash)
                    {
                        my $upc;
                        if ($productID)
                        {
                            my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
                            $upc = $product->upc_ean;
                        }
#                        my $incomeSourceIDHash = $productIDHash->{$productID};

                        my $incomeSourceIDHash = $productIDHash->{$productID}{incomeSourceID};
                        foreach my $incomeSourceID (keys %$incomeSourceIDHash)
                        {
                            my $regionIDHash = $incomeSourceIDHash->{$incomeSourceID};
                            foreach my $regionID (keys %$regionIDHash)
                            {
                                my $channelIDHash = $regionIDHash->{$regionID};
                                foreach my $channelID (keys %$channelIDHash)
                                {
                                    my $priceLevelIDHash = $channelIDHash->{$channelID};
                                    foreach my $priceLevelID (keys %$priceLevelIDHash)
                                    {
                                        my $artistContractTermIDHash = $priceLevelIDHash->{$priceLevelID};
                                        foreach my $artistContractTermID (keys %$artistContractTermIDHash)
                                        {
                                            # Fetch the term. We'll need stuff out of there.
                                            #
                                            my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $artistContractTermID);

                                            my $averagePriceHash = $artistContractTermIDHash->{$artistContractTermID};
                                            foreach my $averagePrice (keys %$averagePriceHash)
                                            {
                                                my $baseRateHash = $averagePriceHash->{$averagePrice};
                                                foreach my $baseRate (keys %$baseRateHash)
                                                {
                                                my $lineItem= $baseRateHash->{$baseRate}{lineitem};

                                                my $usesDefaultRateFlag = 0;
                                                if ($artistContractTermID == $defaultTermID)
                                                {
                                                    $usesDefaultRateFlag = 1;
                                                }

                                                if ($lineItem)
                                                {
                                                    if (RPS::DB::Item::ContractRateType::kRateTypePercentRevenue == $term->contract_rate_type_id)
                                                    {
                                                        $albumNetRevenueIncome += $lineItem->{total};
                                                    }
                                                    else
                                                    {
                                                        $albumUnitLevelIncome += $lineItem->{total};
                                                    }

                                                    my $incomeStatementItem = RPS::DB::Item::ArtistRoyaltyIncomeItem->Create
                                                    (
                                                        artist_royalty_album_id => $albumItem->artist_royalty_album_id,
                                                        album_id => $albumID,
                                                        artist_contract_term_id => $artistContractTermID,

                                                        income_source_id => $incomeSourceID,
                                                        region_id => $regionID,
                                                        channel_id => $channelID,
                                                        price_level_id => $priceLevelID,

                                                        contract_rate_type_id => $term->contract_rate_type_id,
                                                        rate => $baseRate,
#                                                        rate => $lineItem->{baseRate},
                                                        rate_reduction => $lineItem->{rateReduction},
                                                        percentage_of_sales => $lineItem->{percentageOfSales},
                                                        packaging_deduction => $lineItem->{packagingDeduction},
                                                        free_goods_deduction => $lineItem->{freeGoods},
                                                        units_reserved => $lineItem->{unitsReserved},
                                                        revenue_reserved => $lineItem->{dollarsReserved},
                                                        net_units => $lineItem->{netUnits},
                                                        net_revenue => $lineItem->{netRevenue},
                                                        net_rate => $lineItem->{netRate},
                                                        upc_ean => $upc,
                                                        total => $lineItem->{total},
                                                        price => $lineItem->{price},
                                                        sales => $lineItem->{sales},
                                                        returns => $lineItem->{returns},
                                                        revenue => $lineItem->{revenue},
                                                        units_liquidated => $lineItem->{unitsLiquidated},
                                                        revenue_liquidated => $lineItem->{revenueLiquidated},
                                                        uses_default_net_rate => $usesDefaultRateFlag,
                                                    );
                                                    $incomeStatementItem->track_id($trackID) if $trackID;
                                                    $incomeStatementItem->save();

                                                    # Log this sale/line item mapping.
                                                    #
                                                    if ($lineItem->{saleIDs})
                                                    {
                                                        my $statementItemID = $incomeStatementItem->artist_royalty_income_item_id;
                                                        foreach my $saleID (keys %{$lineItem->{saleIDs}})
                                                        {
                                                            my $mi = RPS::DB::Item::SaleRunMap->Create
                                                            (
                                                                sale_id => $saleID,
                                                                run_id => $gRunID,
                                                                statement_item_id => $statementItemID,
                                                                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                                                                status => RPS::DB::Item::SaleRunMap::kStatusPaid,
                                                            );
                                                            $mi->save();
                                                        }
                                                    }


                                                    # !!! Create a reserve item here.
                                                    #
                                                    if ($incomeStatementItem->units_reserved > 0)
                                                    {
                                                        _createUnitReserve($incomeStatementItem, $productID);
                                                    }
                                                    elsif ($incomeStatementItem->revenue_reserved > 0)
                                                    {
                                                        _createRevenueReserve($incomeStatementItem, $productID);
                                                    }
                                                }

                                                my $expenses = $baseRateHash->{$baseRate}{expenses};
                                                foreach my $expenseData (@$expenses)
                                                {
                                                    my $expenseStatementItem = RPS::DB::Item::ArtistRoyaltyExpenseItem->Create
                                                    (
                                                        expense_id => $expenseData->{expenseID},
                                                        uses_default_net_rate => $usesDefaultRateFlag,
                                                        artist_royalty_album_id => $albumItem->artist_royalty_album_id,
                                                        expense_name => $expenseData->{description},
                                                        term_rate => $expenseData->{termRate},
                                                        cost => $expenseData->{cost},
                                                        rate => $expenseData->{rate},
                                                        total => $expenseData->{total},
                                                        memo  => $expenseData->{memo},
                                                    );
                                                    $expenseStatementItem->track_id($trackID) if $trackID;
                                                    $expenseStatementItem->save();
                                                    if ($artistContractTermID == $defaultTermID)
                                                    {
                                                        $albumNetRevenueExpenses += $expenseData->{total};
                                                    }
                                                    else
                                                    {
                                                        $albumRecoupableExpenses += $expenseData->{total};
                                                    }
                                                }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        # Now work on the license income stuff.
                        #
                        my $licenseIncomeIDHash = $productIDHash->{$productID}{licenseIncomeID};
                        foreach my $licenseIncomeID (keys %$licenseIncomeIDHash)
                        {
                            my $rateHash = $licenseIncomeIDHash->{$licenseIncomeID};
                            foreach my $rate (keys %$rateHash)
                            {
                                my $lineData = $rateHash->{$rate}{lineitem};

                                my $units = $lineData->{units};
                                my $revenue = $lineData->{revenue};
                                my $salesHash = $lineData->{sales};

                                my $licenseIncomeData = RPS::DB::Item::LicenseIncome->Lookup(license_income_id => $licenseIncomeID);

                                # Calculate the effective revenue
                                #
                                my $effectiveRevenue = Common::RSMath::round($revenue * ($rate / 100), 2);


                                # Create the proper statement item.
                                #
                                my $liItem = RPS::DB::Item::ArtistRoyaltyLicenseIncomeItem->Create
                                (
                                    license_income_id => $licenseIncomeID,
                                    artist_royalty_statement_id => $statementID,
                                    album_id => $albumID,
                                    track_id => $trackID,
                                    artist_contract_id => $contractID,
                                    license_income_type_id => $licenseIncomeData->license_income_type_id,
                                    rate => $rate,
                                    units => $units,
                                    revenue => $revenue,
                                    net_revenue => $effectiveRevenue,
                                    memo => $licenseIncomeData->memo,
                                );
                                $liItem->save();


                                # Make an entry in the map table.
                                #
                                my $mi = RPS::DB::Item::SaleRunMap->Create
                                (
                                    sale_id => $licenseIncomeData->sale_id,
                                    run_id => $gRunID,
                                    statement_item_id => $liItem->artist_royalty_license_income_item_id,
                                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                                    status => RPS::DB::Item::SaleRunMap::kStatusPaid,
                                );
                                $mi->save();


                                # Update the various related subtotals.
                                # !!! Album id might be 0, if this was just contract matched
                                #
                                $albumLicenseIncomeSubtotal += $effectiveRevenue;
                                if (0 == $albumID)
                                {
                                    $contractLevelLicenseIncomeSubtotal += $effectiveRevenue;
#                                    $contractLevelLicenseIncomeTotal += $effectiveRevenue;
                                }
                            }
                        }
                    }
                }


                # jpk - sure hope all of this adds up...
                #
                my $adjustedNetRevenueExpensesSubtotal = Common::RSMath::round($albumNetRevenueExpenses * ($defaultNetRevenueRate / 100), 4);


                my $albumTotalIncome = $albumUnitLevelIncome + $albumNetRevenueIncome;
                my $albumTotalExpenses = $albumRecoupableExpenses + $adjustedNetRevenueExpensesSubtotal;

                my $albumPreviousBalance = _getAlbumPreviousBalance($payorID, $artistPayeeID, $albumID, $contractID);

                my $albumTotal = $albumTotalIncome - $albumTotalExpenses + $albumPreviousBalance + $albumLicenseIncomeSubtotal;

                $albumItem->unit_level_income($albumUnitLevelIncome);
                $albumItem->net_revenue_income($albumNetRevenueIncome);
                $albumItem->net_revenue_expenses($albumNetRevenueExpenses);
                $albumItem->net_revenue_expenses_subtotal($adjustedNetRevenueExpensesSubtotal);
                $albumItem->recoupable_expenses($albumRecoupableExpenses);
                $albumItem->total_income($albumTotalIncome);
                $albumItem->total_expenses($albumTotalExpenses);
                $albumItem->previous_balance($albumPreviousBalance);
                $albumItem->total($albumTotal);
                $albumItem->license_income_subtotal($albumLicenseIncomeSubtotal);

                my $crossFlag = $gCrossed{$artistPayeeID}{$albumID}{$contractID};
                $albumItem->is_cross_collateralized($crossFlag);

                # !!! Don't save this if the album id is 0.
                # !!! 
                if ($albumID > 0)
                {
                    $albumItem->save();
                }


                # contract-level license income gets lumped in with the 'crossed' album data.
                #
                if ($crossFlag || $albumID == 0)
                {
                    $statementLicenseIncomeSubtotal += $albumLicenseIncomeSubtotal;
                }


                if ($crossFlag) 
                {
                    $crossIncome += $albumTotalIncome;
                    $crossExpense += $albumTotalExpenses;
                    $crossBalance += $albumPreviousBalance;
                    $crossTotal += $albumTotal;

                }
                elsif ($albumID > 0)
                {
                    if ($albumTotal > 0)
                    {
                        $statementSubtotal += $albumTotal;
                    }
                }
                if (0 == $albumID)
                {
                    $crossTotal += $albumLicenseIncomeSubtotal;
                }
            }
        }


        # Are we carrying a balance for the contract level license income?
        # We need to account for that in the crossed total.
        #
        my $contractLevelLicenseIncomePreviousBalance = RPS::DB::Item::ContractLevelLicenseIncomeBalanceAccount->GetCurrentBalance($artistPayeeID, $payorID);
        my $contractLevelLicenseIncomeTotal = $contractLevelLicenseIncomeSubtotal + $contractLevelLicenseIncomePreviousBalance;
        $crossTotal += $contractLevelLicenseIncomePreviousBalance;
        $crossBalance += $contractLevelLicenseIncomePreviousBalance;

#        $crossTotal += $contractLevelLicenseIncomeSubtotal;
        if ($crossTotal > 0)
        {
            $statementSubtotal += $crossTotal;
        }


        # !!! Deal with the pending balances here.
        my $transactionSubtotal;
        if ($payeePendingTransactions)
        {
            while (my $pendingTrans = $payeePendingTransactions->next())
            {
                $transactionSubtotal += $pendingTrans->amount;
                my $statementTransItem = RPS::DB::Item::ArtistRoyaltyTransaction->Create
                (
                 'artist_royalty_statement_id'  => $statementID,
                 'amount'                       => $pendingTrans->amount,
                 'memo'                         => $pendingTrans->memo,
                 'check_number'                 => $pendingTrans->check_number,
                 'pending_transaction_id'       => $pendingTrans->pending_transaction_id,
                 'transaction_date'             => $pendingTrans->transaction_date,
                 'type_code'                    => $pendingTrans->type_code,
                );
                $statementTransItem->save();
            }
        }

        my $payeeBalance = $payeePreviousBalance + $statementSubtotal + $transactionSubtotal;
#        my $statementTotal = $payeePreviousBalance + $statementSubtotal + $transactionSubtotal;



        # see if the minimum payment threshold has been breached.
        #
        my $amountDue = 0;
        if ($payeeBalance >= $minPayment)
        {
            $amountDue = $payeeBalance;
        }


        # If this artist_payee is 'on_hold', then we tag the statement accordingly, and
        # set amount_due to 0.
        #
        my $onHold = 0;
        if (RPS::DB::Item::ArtistPayee::kStatusOnHold == $artistPayeeData->status)
        {
            $onHold = 1;
            $amountDue = 0;
        }

        # Save the grand total
        #
        $statement->previous_balance($payeePreviousBalance);
        $statement->min_payment($minPayment);
        $statement->balance($payeeBalance);
        $statement->total($statementSubtotal);
#        $statement->subtotal($statementSubtotal);
        $statement->amount_due($amountDue);
        $statement->on_hold($onHold);
        $statement->transaction_subtotal($transactionSubtotal);

        $statement->cross_collateralized_income_subtotal($crossIncome);
        $statement->cross_collateralized_expense_subtotal($crossExpense);
        $statement->cross_collateralized_previous_balance($crossBalance);
        $statement->cross_collateralized_subtotal($crossTotal);

        $statement->license_income_subtotal($statementLicenseIncomeSubtotal);
        $statement->contract_level_license_income_subtotal($contractLevelLicenseIncomeSubtotal);
        $statement->contract_level_license_income_total($contractLevelLicenseIncomeTotal);
        $statement->contract_level_license_income_previous_balance($contractLevelLicenseIncomePreviousBalance);

        $statement->save();
    }
    }
}


sub _processExpenses
{
    my ($payors) = @_;

    my %payorIDMap;
    foreach my $payorID (@$payors)
    {
        $payorIDMap{$payorID} = 1;
    }

    # So... I basically have to just grab all the expenses and include them all.
    #
    my $expenses = RPS::DB::Item::Expense->GetAllUnprocessed();
    _report("--- processing expenses");
    my $progressBar = Common::TextProgressBar->new($expenses->size, kSaleProgressQuanta);
    if ($gProgress)
    {
        $| = 1;
        $progressBar->display(\*STDOUT);
    }

    while (my $expense = $expenses->next())
    {
        _report("Expense: " . Dumper($expense), 3);

        # Get the artist_contract_id from the proper contract
        #
        $progressBar->increment();
        if ($gProgress)
        {
            $| = 1;
            $progressBar->display(\*STDOUT);
        }

        my $artistContract;
        my $albumID = 0;
        my $trackID = 0;
        my $contract_type = $expense->parent_type;
        my $crossed = 0;
        if (RPS::DB::Item::Expense::kParentAlbumContract == $contract_type)
        {
            my $albumContract = RPS::DB::Item::AlbumContract->Lookup(album_contract_id => $expense->parent_id);
            if (! $albumContract)
            {
                _report("skipping expense  " . $expense->expense_id . " - cannot find album contract", 3);
                next;
            }
            if (RPS::DB::Item::AlbumContract::kStatusActive != $albumContract->status())
            {
                _report("skipping expense  " . $expense->expense_id . " - album contract is not active", 3);
                next;
            }
            $artistContract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $albumContract->artist_contract_id);
            $albumID = $albumContract->album_id;
            if ($albumContract->cross_collateralized)
            {
                $crossed = 1;
            }
        }
        elsif (RPS::DB::Item::Expense::kParentTrackContract == $contract_type)
        {
            my $trackContract = RPS::DB::Item::TrackContract->Lookup(track_contract_id => $expense->parent_id);
            if (! $trackContract)
            {
                _report("skipping expense ". $expense->expense_id . " - cannot find track contract", 3);
                next;
            }
            if (RPS::DB::Item::TrackContract::kStatusActive != $trackContract->status())
            {
                _report("skipping expense  " . $expense->expense_id . " - track contract is not active", 3);
                next;
            }

            $artistContract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $trackContract->artist_contract_id);
            $trackID = $trackContract->track_id;
            my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID);
            $albumID = $track->album_id;
            if ($trackContract->cross_collateralized)
            {
                $crossed = 1;
            }
        }


        # Skip this expense if the contract belongs to a payor we are not looking at.
        #
        if (! $payorIDMap{ $artistContract->payor_id })
        {
            _report("skipping expense " . $expense->expense_id . " - not the right payor", 3);
           next;
        }


        # Sanity check - do we have a default term for this contract?
        my $artistContractID = $artistContract->artist_contract_id;
#        my $defaultTerm = RPS::DB::Item::NewArtistContractTerm->GetDefault($artistContractID);
#        if (! $defaultTerm)
#        {
#            _report("!!! Skipping expense " . $expense->expense_id . " : artist_contract $artistContractID has no default term");
#            next;
#        }

        if ($crossed)
        {
            $gCrossed{$artistContract->artist_payee_id}{$albumID}{$artistContractID} = 1;
        }

        my $name = _getExpenseName($expense);

        # !!! the 'preProcess' flag seems hokey.
        # I'd rather assign this to the default term id key.
        #
        my $termID = 0;
#        my $termRate = 100;
        if ($expense->pre_process)
        {
            # Get the default net rate term for this artist contract.
            #
            my $term = RPS::DB::Item::NewArtistContractTerm->GetDefault($artistContract->artist_contract_id);
            assert($term, "FATAL ERROR: Missing default term for artist contract: " . $artistContract->artist_contract_id);
            $termID = $term->artist_contract_term_id;
#            $termRate = $term->rate;
        }


        # For the 'Bob' terms, that have the default net rate applied as well, we'll
        # need to fill in the 'termRate' column, and use that in our calculations.
        # (XXX) Which means that we'll need to fetch the default rate?  Or should we do the
        # (XXX) math later?  No, we should do the math here.
        #
        my $expenseRate = $expense->percent;
#        my $netRate = ($termRate * ($expenseRate / 100));
        my $total = Common::RSMath::round(($expense->amount * ($expenseRate / 100)), 4);

        my $expenseData = 
        {
            description => $name,
            cost => $expense->amount,
            rate => $expenseRate,
#            termRate => $termRate,
            total => $total,
            expenseID => $expense->expense_id,
            memo => $expense->memo,
        };

        $expenseRate = Common::RSMath::round($expenseRate, 4);

        push @{$gData
            {$artistContract->payor_id}
            {$artistContract->artist_payee_id}
            {$artistContract->artist_contract_id}
            {$albumID}
            {$trackID}
            {0}    
            {incomeSourceID}
            {0}
            {0}
            {0}
            {0}
            {$termID}   
            {0}
            {$expenseRate}
            {expenses}
            }, $expenseData;
    }

}

sub _processLicenseIncomeSales
{
    my ($payors) = @_;

    # !!! We're only doing 1 payor at a time now...
    #
    my $payorID = $payors->[0];

    _report("--- processing license income sales");

    my $sales = Raptor::DB::Item::Sale->GetUnprocessedLicenseIncomeRoyaltySales();
    _report("unprocess sales count: " . $sales->size, 2);

    my $count = 0;
	while (my $sale = $sales->next())
	{
        _report("SALE: " . Dumper($sale), 4);

        
        # Get the original license income data.
        #
        my $licenseIncomeData = _getLicenseIncomeData($sale->sale_id);
        if (! $licenseIncomeData)
        {
            _logMissedSale($sale, 'Missing license income data', details => 'License income');
            _report("Skipping sale_id " . $sale->sale_id . " : cannot find license income data", 3);
            my $mi = RPS::DB::Item::SaleRunMap->Create
            (
                sale_id => $sale->sale_id,
                run_id => $gRunID,
                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                status => RPS::DB::Item::SaleRunMap::kStatusNoLicenseIncomeData,
            );
            $mi->save();
            next;
        }


        my $albumID = $licenseIncomeData->album_id;
        my $trackID = $licenseIncomeData->track_id;

        # Check the album's 'inactive' flag.
        #
        if ($albumID)
        {
            my $albumData = RPS::DB::Item::Album->Lookup(album_id => $albumID);
            if ($albumData->inactive() || $albumData->status() == 0)
            {
                _report("skipping sale_id " . $sale->sale_id . " : album $albumID is inactive", 3);
                _logMissedSale($sale, "Album inactive", albumID => $albumID, details => 'License income');
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusInactiveAlbum,
                );
                $mi->save();
                next;
            }
        }

        my @contracts;
        if ($licenseIncomeData->contract_id)
        {
            my $contract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $licenseIncomeData->contract_id);

            # Check the payor...
            #
            if ($contract->payor_id != $payorID)
            {
                _report("skipping sale_id " . $sale->sale_id . " : associated contract does not match payor $payorID");
                next;
            }

            # !!! Sanity-check this.
            # !!! The contract MIGHT NOT be associated with this album and/or track anymore.
            #
            if ($albumID)
            {
                my $contracts = RPS::DB::Item::NewArtistContract->GetByPayorAlbumTrack($payorID, $albumID, $trackID);
                my $foundIt = 0;
                while (my $testContract = $contracts->next())
                {
                    if ($testContract->artist_contract_id == $contract->artist_contract_id)
                    {
                        $foundIt = 1;
                        last;
                    }
                }

                if (! $foundIt)
                {
                    _report("skipping sale_id " . $sale->sale_id . " : contract " . $licenseIncomeData->contract_id . " does not appear to be associated with this album/track anymore");
                    _logMissedSale($sale, "Contract no longer matches", albumID => $albumID, contract => $contract, details => 'License income');
                    next;
                }
            }
            push @contracts, $contract;
        }
        else
        {
            # Fetch the artist contracts that match the provided album and/or track id.
            # !!! I don't want contracts associated with inactive albums.
            #
            #my $contracts = RPS::DB::Item::NewArtistContract->GetByPayorAlbumTrack($payorID, $albumID, $trackID);
            # Going to grab matching contracts from all payors.
            #
            my $contracts = RPS::DB::Item::NewArtistContract->GetByAlbumTrack($albumID, $trackID);
            while (my $c = $contracts->next())
            {
                push @contracts, $c;
            }
        }

        $albumID = 0 unless $albumID;
        $trackID = 0 unless $trackID;

        if (! scalar @contracts)
        {
            _report("skipping sale_id " . $sale->sale_id . " : no matching contracts found");
            _logMissedSale($sale, "No matching contracts", albumID => $albumID, trackID => $trackID, details => 'License income');
            next;
        }


        # Build a hash that contains the contract ids that have already paid on this sale.
        # !!! Maybe I can re-use the current method?
        #
        my $previousContracts = _getPaidContractIDsForSale($sale);
        _report("previous contracts: " . Dumper($previousContracts), 4);



        foreach my $contract (@contracts)
        {
            # First, make sure the contract is associated with the correct payor.
            #
            if ($contract->payor_id != $payorID)
            {
                next;   
            }
            
            # !!! CHECK to see whether we've paid on this contract already...
            #
            if ($previousContracts->{$contract->artist_contract_id}{$trackID})
            {
                _report("skipping contract " . $contract->artist_contract_id . " : already paid", 3);
                next;
            }

            # fetch the contract's 'term' data, to see whether we _really_ pay or not.
            #
            my $term = RPS::DB::Item::ArtistContractLicenseIncome->Lookup(
             artist_contract_id => $contract->artist_contract_id, 
             license_income_type_id => $licenseIncomeData->license_income_type_id,
             inactive => 0,
            );
            
            next unless $term;
            next if $term->inactive;

            _report("+++ matching contract " . $contract->artist_contract_id . " with this term: " . Dumper($term), 4);

            my $rate = $term->percent;
            $rate = 0 unless $rate;

            $gData
            {$payorID}
            {$contract->artist_payee_id}
            {$contract->artist_contract_id}
            {$albumID}
            {$trackID}
            {0}
            {licenseIncomeID}
            {$licenseIncomeData->license_income_id}
            {$rate}
            {lineitem}
            {units} += $licenseIncomeData->units;

            $gData
            {$payorID}
            {$contract->artist_payee_id}
            {$contract->artist_contract_id}
            {$albumID}
            {$trackID}
            {0}
            {licenseIncomeID}
            {$licenseIncomeData->license_income_id}
            {$rate}
            {lineitem}
            {revenue} += $licenseIncomeData->revenue;
        }
    }
}


sub _processSales
{
    my ($payors) = @_;
    
    my $payorID = $payors->[0];


    _report("--- processing sales");
    my $sales = Raptor::DB::Item::Sale->GetUnprocessedRoyaltySales();

    _report("unprocess sales count: " . $sales->size, 2);

    my $progressBar = Common::TextProgressBar->new($sales->size, kSaleProgressQuanta);
    if ($gProgress)
    {
        $| = 1;
        $progressBar->display(\*STDOUT);
    }

    my %badProducts;

    my $count = 0;
	while (my $sale = $sales->next())
	{
        _report("SALE: " . Dumper($sale), 4);

        $progressBar->increment();
        if ($gProgress)
        {
            $| = 1;
            $progressBar->display(\*STDOUT);
        }

        
        # Fetch the product this sale references.
        #
        # !!!
        # There will be some sales that do not have a product_id.
        # Namely, master use and sync sales.
        # These will just have a track_id.
        #
        # This means that I cannot use product_id as a key into the statement item table.
        # I am going to have to use album_id/track_id
        #
        my $productID = $sale->product_id;

        
        # We need the album_id, and the track_id if this is a track sale.
        #
        my $albumID;
        my $trackID;

        my $missingProductPriceFlag = 0;
        if ($productID)
        {
            my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
            if (! $product)
            {
                _logMissedSale($sale, 'Invalid product id');
                _report("skipping sale_id " . $sale->sale_id . " : product $productID cannot be found", 3);
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusBadProductID,
                );
                $mi->save();
                next;
            }

            # Skip inactive products.
            #
            if (RPS::DB::Item::Product::kProductStatusActive != $product->product_status_id)
            {
                _report("skipping sale_id " . $sale->sale_id . " : product $productID is not active", 3);
                ($albumID, $trackID) = _getAlbumTrackFromProduct($product);
                my $details = "Product $productID, type " . _idToProductType($product->product_type_id);
                _logMissedSale($sale, "Product inactive", productID => $productID, albumID => $albumID, trackID => $trackID, details => $details);
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusInactiveProduct,
                );
                $mi->save();
                next;
            }

            # !!! Sanity check.
            # !!! If we don't have any entries in the product_price table for this product,
            # !!! then we might not be able to pay on it... We won't know for certain
            # !!! until we've fetched the contracts.
            #
            # If this is a track product... well, we don't have entries for track products.
            # Have to use the album product entry instead.
            #
            my $productIDToCheck = $productID;
            if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
            {
                my $trackID = $product->asset_id;
                my $trackData = RPS::DB::Item::Track->Lookup(track_id => $trackID);
                my $albumID = $trackData->album_id;

                # Find the digital album product id
                #
                my $digitalAlbumProductList = RPS::DB::Item::Product->GetProductsByAlbumID($albumID, RPS::DB::Item::Product::kProductTypeDigital);
                if ($digitalAlbumProductList && $digitalAlbumProductList->size() > 0)
                {
                    my $digitalAlbumProduct = $digitalAlbumProductList->next();
                    $productIDToCheck = $digitalAlbumProduct->product_id;
                }
            }
            my $productPrices = RPS::DB::Item::ProductPrice->GetByProductID($productIDToCheck);
            if (! $productPrices || 0 == $productPrices->size())
            {
                _report("missing product price", 3);
                $missingProductPriceFlag = 1;
            }

            # (XXX) Need to take this a step further - is there the correct price in the table?
            #

            ($albumID, $trackID) = _getAlbumTrackFromProduct($product);
        }
        else
        {
            # XXX This field name will probably change
            #
            $trackID = $sale->lic_track_id;
            if (! $trackID)
            {
                _logMissedSale($sale, 'No track id or product id');
                _report("skipping sale_id " . $sale->sale_id . " : no product id or lic_track_id ", 3);
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusNoLicTrackOrProductID,
                );
                $mi->save();

                next;
            }
            my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID);
            $albumID = $track->album_id;
        }

        # Check the album's 'inactive' flag.
        #
        if ($albumID)
        {
            my $albumData = RPS::DB::Item::Album->Lookup(album_id => $albumID);
            if ($albumData->inactive() || $albumData->status() == 0)
#            if ($albumData->inactive())
            {
                _report("skipping sale_id " . $sale->sale_id . " : album $albumID is inactive", 3);
                _logMissedSale($sale, "Album inactive", productID => $productID, albumID => $albumID, trackID => $trackID);
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusInactiveAlbum,
                );
                $mi->save();
                next;
            }
        }


        
        my $countryCode = $sale->country_code;
        my $regionID;
        my $channelID = $sale->channel;
        my $priceLevelID = $sale->price_level;
        my $incomeSourceID = _determineIncomeSourceID($sale->product_type, $sale->format_type);

        if (! $incomeSourceID)
        {
            # !!! going to skip this sale
            #
            _report("skipping sale_id " . $sale->sale_id . " :  could not find income source id", 3);
            _logMissedSale($sale, "No matching income source",
             details => 'No income source id matches product_type ' . _idToSaleProductType($sale->product_type) . ', format type ' . _idToFormatType($sale->format_type),
             productID => $productID, albumID => $albumID, trackID => $trackID);
            my $mi = RPS::DB::Item::SaleRunMap->Create
            (
                sale_id => $sale->sale_id,
                run_id => $gRunID,
                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                status => RPS::DB::Item::SaleRunMap::kStatusNoMatchingIncomeSourceID,
            );
            $mi->save();
            next;
        }

        my $clientID = Common::RSApp::GetClientID();
        if (123 == $clientID) # WELK
        {
            if (RPS::DB::Item::IncomeSource::kIncomeSourceDigitalStream == $incomeSourceID
             || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTethered == $incomeSourceID)
            {
                _logMissedSale($sale, "Skipping streams and tethered",
                 details => 'Skipping stream and tethered download sales per client request - product type ' . _idToSaleProductType($sale->product_type) . ', format_type ' . _idToFormatType($sale->format_type),
                productID => $productID, albumID => $albumID, trackID => $trackID);
                my $mi = RPS::DB::Item::SaleRunMap->Create
                (
                    sale_id => $sale->sale_id,
                    run_id => $gRunID,
                    run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                    status => RPS::DB::Item::SaleRunMap::kStatusSkipped,
                );
                $mi->save();
                next;
            }
        }




        my $units = $sale->units;
        my $numSales = $sale->sales;
        my $numReturns = $sale->returns;
        my $averagePrice = $sale->average_price;


        my $isDigitalFlag;
        if ($productID)
        {
            my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
            my $productTypeID = $product->product_type_id;
            if (RPS::DB::Item::Product::kProductTypeDigital == $productTypeID
             || RPS::DB::Item::Product::kProductTypeDigitalTrack == $productTypeID)
            {
                $isDigitalFlag = 1;
                if ($units < 0)
                {
                    $numReturns = (-1 * $units);
                }
                else
                {
                    $numSales = $units;
                }

                # Digital sales don't set the price_level_id.  I will have to hard-code it.
                #
                if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $productTypeID)
                {
                    $priceLevelID = RPS::DB::Item::PriceLevel::kPriceLevelTrackDownload;
                }
                if (RPS::DB::Item::Product::kProductTypeDigital == $productTypeID)
                {
                    $priceLevelID = RPS::DB::Item::PriceLevel::kPriceLevelAlbumDownload;
                }
            }
            else
            {
                $units = $numSales - $numReturns;

                # make sure there is a price level.  If the sale didn't specify one, use
                # the default from the product.
                #
                if (! $priceLevelID)
                {
                    $priceLevelID = $product->default_price_level_id;
                }
            }


            # There ought to be an entry in the product_price table... if not, we might not be able to pay.
            # (XXX) But, until I know which term we're using, I won't be sure if it even matters...

            
        }
        else
        {
            # !!! If there isn't a productID, then this is some sort of 'weird' sale, like a master use.
            # Seems like all of these have different ways of encoding their units.
            # I'll default to using units, unless there aren't any.
            #
            if (RPS::DB::Item::IncomeSource::kIncomeSourceMasterLicense == $incomeSourceID
             || RPS::DB::Item::IncomeSource::kIncomeSourceSyncLicense == $incomeSourceID)
            {
                $isDigitalFlag = 1;
            }

            if ($units)
            {
                if ($units < 0)
                {
                    $numReturns = (-1 * $units);
                }
                else
                {
                    $numSales = $units;
                }
            }
            else
            {
                $units = $numSales - $numReturns;
            }
        }


        # !!! Bottom line - after this code block net units will be $numSales - $numReturns.



        # This routine returns an array of hashes that look like this:
        #{
        #    artistContract => $artistContract,
        #    albumID => $albumID,
        #    trackID => $trackContract->track_id,
        #};

        # Make note of crossing here, too.
        #
        # !!! Can I specify the list of payor ids here?
        # !!! It would be convenient if this did not return contracts for inactive payees...
        #
        # We're going to check contracts for all payors for a match.
        # This way, we can suppres the "No matching contracts" message if there
        # is a match for a different payor.
        my $allPayorIDs = _getAllPayorIDs();;
        my $contractList = RPS::ArtistRoyalty::Utils::GetMatchingContracts($albumID, $trackID, $allPayorIDs);

        # Need to loop through this twice - the first time so I can sanity check the product_price situation.
        #
        # !!! Need to extend this test to see whether we've got the _correct_ price_level_id in the product_price table.
        my $skipThisSale = 0;
        if (0 == scalar @$contractList)
        {
            _logMissedSale($sale, 'No matching contracts', productID => $productID, albumID => $albumID, trackID => $trackID);
            _report("...no matching contracts found, skipping sale " . $sale->sale_id, 3);
            my $mi = RPS::DB::Item::SaleRunMap->Create
            (
                sale_id => $sale->sale_id,
                run_id => $gRunID,
                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                status => RPS::DB::Item::SaleRunMap::kStatusNoMatchingContracts,
            );
            $mi->save();
            next;
        }



        foreach my $contractHash (@$contractList)
        {
            my $contract = $contractHash->{artistContract};
            
            # Since $contractList now contains contracts for all payors,
            # we need to filter out all of them except for the ones for this payor.
            #
            if ($contract->payor_id != $payorID)
            {
                next;   
            }


            my $term = RPS::ArtistRoyalty::Utils::GetMatchingTerm($contract, $countryCode, $channelID, $incomeSourceID, $priceLevelID);
            if (! $term)
            {
                next;
            }
            my $rateType = $term->contract_rate_type_id;

            if (RPS::DB::Item::ContractRateType::kRateTypePercentRetail == $rateType
             || RPS::DB::Item::ContractRateType::kRateTypePercentWholesale == $rateType
             || RPS::DB::Item::ContractRateType::kRateTypePercentPPD == $rateType)
            {
                # Do we have an entry in the product price table?
                #
                my $productPrice = _getPrice($priceLevelID, $productID);
                if (! $productPrice)
                {
                    _logMissedSale($sale, "No product price", details => "No product price entry for price level " . _idToPriceLevel($priceLevelID), 
                     productID => $productID, albumID => $albumID, trackID => $trackID, contract => $contract,
                    );
                    _report("!!! skipping this sale : matched term " . Dumper($term) . " but didn't have a product price entry", 3);

                    $badProducts{$productID} = 1;
                    $skipThisSale = 1;
                    last;
                }
            }
        }


        if ($skipThisSale)
        {
            _report(" ...skipping sale", 3);
            my $mi = RPS::DB::Item::SaleRunMap->Create
            (
                sale_id => $sale->sale_id,
                run_id => $gRunID,
                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                status => RPS::DB::Item::SaleRunMap::kStatusNoProductPrice,
            );
            $mi->save();
            next;
        }


        # Build a hash that contains the contract ids that have already paid on this
        # sale, and any associated track ids.
        #
        my $previousContracts = _getPaidContractIDsForSale($sale);

        # We'll keep track of the original track id before we iterate through the contracts.
        # This is because we want to set the track_id to whatever value any _track_ contracts
        # have set up, so it displays properly... And we'll need to set it back to the original
        # value afterwards.
        #
        my $origTrackID = $trackID;

        my $termsMatched = 0;
        foreach my $contractHash (@$contractList)
        {
            
            my $contract = $contractHash->{artistContract};
            
            # Since $contractList now contains contracts for all payors,
            # we need to filter out all of them except for the ones for this payor.
            #
            if ($contract->payor_id != $payorID)
            {
                next;   
            }    
             
            
            _report(" contractHash : " . Dumper($contractHash), 4);       

            # Restore the original track_id, in case a previous contract modified it.
            #
            $trackID = $origTrackID;

            # If this contract was attached via a TrackContract, then the hash will have a trackID.
            # We'll use that.
            # There will also now be a 'prorateTrackCount', to be used for prorating.
            #
            my $prorateTrackCount = 1;
            if ($contractHash->{trackID})
            {
                $trackID = $contractHash->{trackID};
                if ($contractHash->{prorateTrackCount})
                {
                    $prorateTrackCount = $contractHash->{prorateTrackCount};
                }
            }


            # Now we can check to see whether we've already paid this sale for this contract for this track.
            #
            my $checkTrackID = 0;
            $checkTrackID = $contractHash->{trackID} if $contractHash->{trackID};

            if ($previousContracts->{$contract->artist_contract_id}{$checkTrackID})
            {
                _report("skipping contract " . $contract->artist_contract_id . " : already paid", 3);
                next;
            }
            if ($origTrackID && $previousContracts->{$contract->artist_contract_id}{$origTrackID})
            {
                _report("skipping contract " . $contract->artist_contract_id . " : already paid", 3);
                next;
            }

            my $term = RPS::ArtistRoyalty::Utils::GetMatchingTerm($contract, $countryCode, $channelID, $incomeSourceID, $priceLevelID);

            if (! $term)
            {
                # Let's grab the income source so that we can modify the message based on its format
                #
                my $incomeSource = RPS::DB::Item::IncomeSource->Lookup(income_source_id => $incomeSourceID);
                my $details = "No term found for country code $countryCode, income source " . $incomeSource->name;
                
                if ($incomeSource->format == RPS::DB::Item::IncomeSource::kFormatPhysical)
                {                    
                    $details .= ", price level " . _idToPriceLevel($priceLevelID) . ", channel " . _idToChannel($channelID);
                }
                
                _report("skipping sale for contract " . $contract->artist_contract_id . " - no matching terms", 3);
                _logMissedSale($sale, "No matching term", details => $details, productID => $productID, trackID => $trackID, albumID => $albumID, contract => $contract );

                # !!! Do I want to add this to the SaleRunMap?
                next;
            }

            # Get region id from the term
            #
            $regionID = $term->region_id;
            $regionID = 0 unless $regionID;

            # !!! I need to deal with the default term in a special way.... So I will need to know whether
            # !!! this term is the default.
            # !!! I think the 'priority=0' will suffice.
            #

            # Total revenue is calculated differently for physical and digital sales.
            # And is only relevant for net revenue terms.
            #
            my $totalRevenue = $sale->total_revenue * $sale->conversion_rate;
            my $rateTypeID = $term->contract_rate_type_id;
            if (RPS::DB::Item::ContractRateType::kRateTypePercentRevenue == $rateTypeID)
            {
                if ($isDigitalFlag)
                {
                    # !!! For Instinct, and other labels that do this, we'll need to
                    # determine the mechanical stat rate, and _subtract_ that from the totalRevenue.
                    # This would be the place to do that.
                    # (!!!) Is this just for track sales?   Probably not.  So, for an album sale, I will have to
                    # (!!!) determine the stat rate on all tracks on the album (except for bonus tracks?)
                    #
                    my $price = $sale->price;

                
                    # Only do this for US sales... and only for downloads.
                    #
                    if (_isDownload($incomeSourceID) && 'US' eq $sale->country_code() && _subtractStatRateFromNetRevenue())
                    {
                        my $statRatePrice = _determineStatRatePrice($sale);
                        _report("  price: $price statRatePrice: $statRatePrice new price " . ($price - $statRatePrice), 3);
                        $price -= $statRatePrice;
                    }
                    $totalRevenue = $price * $units * $sale->conversion_rate;
                }

                # Apply the distribution fee reduction, if there is one.
                #
                my $distributionFee = _getDistributionFeeFromSale($sale);
                if ($distributionFee)
                {
                    $totalRevenue = Common::RSMath::round($totalRevenue * ((100 - $distributionFee)/100), 2);
                }
            }


            # !!!!!!! I _think_ I can get the price here, and stuff it into $averagePrice.
            # !!!!!!! Rather than doing it in _fillInData.

            # Average price is only relevant for percentage of average price deals - for the 
            # rest, I want to ignore whatever junk may be in the sale table.
            #
            if (RPS::DB::Item::ContractRateType::kRateTypePercentAverage != $rateTypeID)
            {
                $averagePrice = 0;
            }

            _report(" totalRevenue $totalRevenue rateTypeID $rateTypeID isDigital $isDigitalFlag units $units sales $numSales returns $numReturns price " . $sale->price . " conversion_rate " . $sale->conversion_rate . " TERM: " . Dumper($term), 4);


            # Apply the prorate to the rate (if this is an album sale)
            # JPK - Check the sale product_type value first, then look at the income source.
            #
            my $rate = $term->rate;
            if (RPS::File::Sale::TYPE_ALBUM eq $sale->product_type
             || _isAlbumIncomeSource($incomeSourceID))
            {
                _report("prorateTrackCount: $prorateTrackCount : pre proration rate = $rate", 3);
                $rate /= $prorateTrackCount;
                _report("   post proration rate = $rate", 3);
            }


            # !!! Note that we're passing in priceLevelID.
            # !!! Rather than 'price' itself.
            # !!! EXCEPT that this will not work for stupid master use sales, so we need to pass price, too.
            #
            $termsMatched++;

            # !!!
            # I want to be able to log _exactly_ which statement item(s) this sale will land on.
            # But... I don't know that yet!
            #
            _createStatementData($numSales, $numReturns, $rate, $totalRevenue, $averagePrice, $term, $albumID, $trackID, $productID, $incomeSourceID, $regionID, $channelID, $priceLevelID, $contract, $rateTypeID, $sale->price, $sale->sale_id);
        }


        my $mapStatus;
        if (! $termsMatched)
        {
            my $mi = RPS::DB::Item::SaleRunMap->Create
            (
                sale_id => $sale->sale_id,
                run_id => $gRunID,
                run_type => RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty,
                status =>  RPS::DB::Item::SaleRunMap::kStatusNoContractTerm,
            );
            $mi->save();
        }

    }

    my @badProductIDs = sort keys %badProducts;
    foreach my $badProductID (@badProductIDs)
    {
        _report("!!! product_id $badProductID missing product_price map entry, skipping associated sales", 2);
    }


}

sub _fillInData
{
    # Ok, so we now have all the units accumulated.  We can now apply rates, deductions, etc.
    #
    _report("--- calculating royalties");
    _report("DATA: " . Dumper(\%gData), 4);

    my $progressBar = Common::TextProgressBar->new(scalar (keys %gData), kSaleProgressQuanta);
    if ($gProgress)
    {
        $| = 1;
        $progressBar->display(\*STDOUT);
    }

    foreach my $payorID (keys %gData)
    {
    my $artistPayeeHash = $gData{$payorID};
    foreach my $artistPayeeID (keys %$artistPayeeHash)
    {
        _report("_fillInData artistPayeeID $artistPayeeID", 3);
        $progressBar->increment();
        if ($gProgress)
        {
            $| = 1;
            $progressBar->display(\*STDOUT);
        }

        my $contractIDHash = $artistPayeeHash->{$artistPayeeID};
        foreach my $contractID (keys %$contractIDHash)
        {
            _report("_fillInData contractID $contractID", 3);
            my $defaultTerm = RPS::DB::Item::NewArtistContractTerm->GetDefault($contractID);
            my $defaultTermID = 0;
            my $defaultNetRevenueRate = 100;
            if ($defaultTerm)
            {
                $defaultTermID = $defaultTerm->artist_contract_term_id;
                $defaultNetRevenueRate = $defaultTerm->rate;
            }

            my $albumIDHash = $contractIDHash->{$contractID};
            foreach my $albumID (keys %$albumIDHash)
            {
                my $trackIDHash = $albumIDHash->{$albumID};
                foreach my $trackID (keys %$trackIDHash)
                {
                    my $productIDHash = $trackIDHash->{$trackID};
#_report("Pre-loop PRODUCT ID HASH: " . Dumper($productIDHash), 4);
                    foreach my $productID (keys %$productIDHash)
                    {
                        my $incomeSourceIDHash = $productIDHash->{$productID}{incomeSourceID};
                        foreach my $incomeSourceID (keys %$incomeSourceIDHash)
                        {
                            my $regionIDHash = $incomeSourceIDHash->{$incomeSourceID};
                            foreach my $regionID (keys %$regionIDHash)
                            {
                                my $channelIDHash = $regionIDHash->{$regionID};
                                foreach my $channelID (keys %$channelIDHash)
                                {
                                    my $priceLevelIDHash = $channelIDHash->{$channelID};
                                    foreach my $priceLevelID (keys %$priceLevelIDHash)
                                    {
                                        my $artistContractTermIDHash = $priceLevelIDHash->{$priceLevelID};
                                        foreach my $artistContractTermID (keys %$artistContractTermIDHash)
                                        {
                                            my $averagePriceHash = $artistContractTermIDHash->{$artistContractTermID};
                                            foreach my $price (keys %$averagePriceHash)
                                            {
                                                my $baseRateHash = $averagePriceHash->{$price};
                                                foreach my $baseRate (keys %$baseRateHash)
                                                {
                                                _report("_fillInData: $artistPayeeID, $contractID, $albumID, $trackID, $productID, $incomeSourceID, $regionID, $channelID, $priceLevelID, $artistContractTermID, $price", 3);
                                                my $lineItemRecord = $baseRateHash->{$baseRate}{lineitem};

                                                my $sales =             $lineItemRecord->{sales};
                                                my $returns =           $lineItemRecord->{returns};
                                                my $unitsLiquidated =   $lineItemRecord->{unitsLiquidated};
                                                my $dollarsLiquidated = $lineItemRecord->{revenueLiquidated};
                                                my $totalRevenue =      $lineItemRecord->{revenue};


                                                my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $artistContractTermID);

                                                my $contract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $contractID);


                                                my $rateType = $term->contract_rate_type_id;
#                                                my $baseRate = $term->rate;
                                                my $rateReduction = $term->rate_reduction;
                                                my $percentageOfSales = $term->percentage_of_sales;
                                                my $packagingDeduction = $term->packaging_deduction;
                                                my $freeGoods = $term->free_goods_deduction;
                                                my $reservePercentage = $contract->reserve_rate;
                                                

                                                my $unitsReserved;
                                                my $dollarsReserved;
                                                my $netUnits;
                                                my $netRevenue;
                                                my $netRate;
                                                my $total;
#                                                my $price;

                                                my $actualPriceLevelID = $priceLevelID;
                                                my $usingDefaultRate = 0;
                                                if ($artistContractTermID == $defaultTermID)
                                                {
                                                    $usingDefaultRate = 1;
                                                }

                                                if (RPS::DB::Item::ContractRateType::kRateTypePercentRevenue == $rateType)
                                                {
                                                    $totalRevenue = Common::RSMath::round($totalRevenue, 2);

                                                    ($dollarsReserved, $netRevenue, $netRate, $total) = 
                                                        _netRevenueBased($productID, $totalRevenue, $baseRate, $rateReduction, 
                                                            $percentageOfSales, $packagingDeduction, $freeGoods, $dollarsLiquidated, 
                                                            $reservePercentage);

                                                    # jpk - we do want to display the net units (customer request)
                                                    #
                                                    $netUnits = $sales - $returns;



                                                }
                                                else
                                                {
                                                    # If the user hasn't entered a price... well, we're hosed.
                                                    # We should probably skip this _sale_.
                                                    # (XXX) Trouble is, we're long past the point where that
                                                    # would be practical.  We'll have to do this in a pre-processing step.
                                                    # 
                                                    if (! $price)
                                                    {
                                                        _report("term that could not be matched to a price: " . Dumper($term), 2);
                                                        next;
                                                    }

                                                    ($unitsReserved, $netUnits, $netRate, $total) = 
                                                     _priceBased($productID, $price, $baseRate, $rateReduction, $percentageOfSales, $packagingDeduction,
                                                     $freeGoods, $sales, $returns, $unitsLiquidated, $reservePercentage, $rateType);

                                                    # jpk - we don't want to display the revenue data, since it's
                                                    # meaningless (and misleading).
                                                    # It would be nice if the sales records didn't have any data in these
                                                    # columns, but we can't rely on that.
                                                    #
#                                                    $lineItemRecord->{revenue} = 0;
                                                }


                                                # Create the statement and statement items
                                                # !!!
                                                #
                                                # Let's not create actual statement items : Instead, store these calculated stuff
                                                # in the record, and we'll loop through it again later.
                                                #
                                                $lineItemRecord->{unitsReserved}        = $unitsReserved;
                                                $lineItemRecord->{dollarsReserved}      = $dollarsReserved;
                                                $lineItemRecord->{netUnits}             = $netUnits;
                                                $lineItemRecord->{netRevenue}           = $netRevenue;
                                                $lineItemRecord->{netRate}              = $netRate;
                                                $lineItemRecord->{total}                = $total;
                                                $lineItemRecord->{price}                = $price;
                                                $lineItemRecord->{baseRate}             = $baseRate;
                                                $lineItemRecord->{rateReduction}        = $rateReduction;
                                                $lineItemRecord->{percentageOfSales}    = $percentageOfSales;
                                                $lineItemRecord->{packagingDeduction}   = $packagingDeduction;
                                                $lineItemRecord->{freeGoods}            = $freeGoods;
                                                $lineItemRecord->{reservePercentage}    = $reservePercentage;
                                                $lineItemRecord->{actualPriceLevelID}   = $actualPriceLevelID;

                                            }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    }
}

sub _netRevenueBased
{
    my ($productID, $totalRevenue, $baseRate, $rateReduction, $percentageOfSales, $packagingDeduction, $freeGoods, $dollarsLiquidated, $reservePercentage) = @_;

    my $dollarsReserved;
    my $netRevenue;
    my $netRate;
    my $total;


    _report("_netRevenueBased: productID $productID totalRevenue $totalRevenue  br $baseRate  rr $rateReduction  pos $percentageOfSales pd $packagingDeduction fg $freeGoods dl $dollarsLiquidated rp $reservePercentage", 2);

    # Take reserves before deductions.
    #
    $netRevenue = $totalRevenue;
    if ($netRevenue > 0 && _canTakeReserves($productID) && $reservePercentage)
    {
        $dollarsReserved = Common::RSMath::round($netRevenue * ($reservePercentage / 100), 2);
        $netRevenue -= $dollarsReserved;

        _report("  took reserves. dollars reserved: $dollarsReserved  new net revenue $netRevenue", 2);
    }


    # Add in liquidated units
    #
    $netRevenue += $dollarsLiquidated;
    _report("  added in liquidated units of $dollarsLiquidated, netRevenue $netRevenue", 2);


    if ($netRevenue > 0)
    {
        $netRevenue = Common::RSMath::round($netRevenue * ((100 - $freeGoods)/100), 2) if $freeGoods;
        _report("   free goods deduction of " . (1 - $freeGoods) . "yields $netRevenue", 2);
    }


    # Apply rate deductions
    # !!! Do these apply at all, really?
    #
    $netRate = $baseRate;

    $netRate = ($netRate * ($percentageOfSales / 100)) if $percentageOfSales;
    _report("   percentage of sales of $percentageOfSales yields rate of $netRate", 2);

    $netRate = ($netRate * ($rateReduction / 100)) if $rateReduction;
    _report("   rate reduction of $rateReduction yields rate of $netRate", 2);

    $netRate = ($netRate * ((100 - $packagingDeduction) / 100)) if $packagingDeduction;
    _report("   packaging reduction of " . (1 - $packagingDeduction) . " yields rate of $netRate", 2);

    

    $total = Common::RSMath::round((($netRate / 100) * $netRevenue), 2);
    _report("   TOTAL: $total  (($netRate / 100) * $netRevenue)", 2);


    return ($dollarsReserved, $netRevenue, $netRate, $total);
}

sub _priceBased
{
    my ($productID, $price, $baseRate, $rateReduction, $percentageOfSales, $packagingDeduction, $freeGoods, $sales, $returns, $unitsLiquidated, $reservePercentage, $rateType) = @_;

    my $unitsReserved;
    my $netUnits;
    my $netRate;
    my $total;

    _report("_priceBased: productID $productID price $price br $baseRate  rr $rateReduction  pos $percentageOfSales pd $packagingDeduction fg $freeGoods sales $sales returns $returns ul $unitsLiquidated rp $reservePercentage", 2);


    # According to Catie, I need to apply the percentageOfSales deduction FIRST
    #
    if ($sales > 0 && $percentageOfSales > 0)
    {
        $sales = Common::RSMath::round($sales * ($percentageOfSales / 100), 0);
        _report("  after percentage of sales deduction of $percentageOfSales, sales = $sales", 2);
    }
#    $netRate = ($netRate * ($percentageOfSales / 100)) if $percentageOfSales;
#    _report("  after pos , netRate = $netRate", 2);

    # Take reserves before deductions.
    #
    if ($sales > 0 && _canTakeReserves($productID) && $reservePercentage)
    {
        $unitsReserved = Common::RSMath::round($sales * ($reservePercentage / 100), 0);
        if ($unitsReserved > $sales)
        {
            $unitsReserved = $sales;
        }
        $sales -= $unitsReserved;
    }
    _report("  after reserves, sales = $sales ", 2);

    # Add in liquidated units
    #
    $netUnits = $sales;
    $netUnits += $unitsLiquidated;
    _report("  netUnits = $netUnits after liquidating $unitsLiquidated units", 2);


    # Take free goods next
    #
    if ($netUnits > 0 && $freeGoods)
    {
        $netUnits = Common::RSMath::round($netUnits * ((100 - $freeGoods)/100), 0);
        _report("  after free goods, netUnits = $netUnits", 2);
    }



    $netUnits -= $returns;
    _report("  netUnits = $netUnits after subtracting returns of $returns", 2);





    # Apply rate deductions
    #
    $netRate = $price;
    _report("  netRate = $netRate", 2);
    if (RPS::DB::Item::ContractRateType::kRateTypeFixed == $rateType)
    {
        $netRate = ($netRate * $baseRate);
    }
    else
    {
        $netRate = ($netRate * ($baseRate / 100));
    }
    _report("  after base rate applied, netRate = $netRate", 2);

# !!! Sigh  -  NOW they tell me that percentageOfSales is _NOT_ a rate reduction, but a unit reduction.
#    $netRate = ($netRate * ($percentageOfSales / 100)) if $percentageOfSales;
#    _report("  after pos , netRate = $netRate", 2);

    $netRate = ($netRate * ($rateReduction / 100)) if $rateReduction;
    _report("  after rate reduction applied, netRate = $netRate", 2);

    $netRate = ($netRate * ((100 - $packagingDeduction) / 100)) if $packagingDeduction;
    _report("  after packaging applied , netRate = $netRate", 2);

    

    $total = Common::RSMath::round(($netRate * $netUnits), 2);
    _report("  total = $total", 2);

    return ($unitsReserved, $netUnits, $netRate, $total);
}


sub _getRetailPrice
{
    my ($priceLevelID, $productID) = @_;
    my ($price, $actualPriceLevelID) = _getPrice($priceLevelID, $productID);
    
    return ($price->retail, $actualPriceLevelID) if $price;
    return (undef, undef);
}

sub _getWholesalePrice
{
    my ($priceLevelID, $productID) = @_;
    my ($price, $actualPriceLevelID) = _getPrice($priceLevelID, $productID);

    return ($price->wholesale, $actualPriceLevelID) if $price;
    return (undef, undef);
}

sub _getPPDPrice
{
    my ($priceLevelID, $productID) = @_;
    my ($price, $actualPriceLevelID) = _getPrice($priceLevelID, $productID);

    return ($price->ppd, $actualPriceLevelID) if $price;
    return (undef, undef);
}



sub _getPrice
{
    my ($priceLevelID, $productID) = @_;

    # First, fetch the product.
    # We need to see if this is a track product... if it is, we'll need
    # to get the album product id.
    # This is because we don't have product_price entries for track products...
    #
    my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
    if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
    {   
        my $albumID;
        my $trackID = $product->asset_id;

        # Fetch this from the track's data.
        #
        my $trackData = RPS::DB::Item::Track->Lookup(track_id => $trackID);
        assert($trackData);
        $albumID = $trackData->album_id;

        # Find the digital album product id
        #
        my $digitalAlbumProductList = RPS::DB::Item::Product->GetProductsByAlbumID($albumID, RPS::DB::Item::Product::kProductTypeDigital);
        if ($digitalAlbumProductList)
        {   
            $product = $digitalAlbumProductList->next();
            $productID = $product->product_id;
        }
    }

    if (0 == $priceLevelID)
    {   
        $priceLevelID = $product->default_price_level_id;
    }


    my $productPrice = RPS::DB::Item::ProductPrice->Lookup(product_id => $productID, price_level_id => $priceLevelID);
    if (! $productPrice)
    {   
        warn "\nError - can't fetch ProductPrice product_id $productID  price_level_id $priceLevelID\n";
        return undef;
    }
    my $price = RPS::DB::Item::Price->Lookup(price_id => $productPrice->price_id);

    return ($price, $priceLevelID);
}


sub _createStatementData
{
    my ($sales, $returns, $rate, $totalRevenue, $averagePrice, $term, $albumID, $trackID, $productID, $incomeSourceID, $regionID, $channelID, $priceLevelID, $contract, $rateTypeID, $price, $saleID) = @_;


    my $payorID = $contract->payor_id;
    assert($payorID);

    # Round the rate, to make sure we are always hashing on the same things
    # (i.e. 40 vs 40.0000)
    #
    $rate = Common::RSMath::round($rate, 4);

#    my $price;

    # !!! Putting this in here to try and get master use sales to work...
    if ($productID)
    {
        ($price, $priceLevelID) = _determinePrice($rateTypeID, $averagePrice, $priceLevelID, $productID);
    }

    # !!! price and rate will need to be rounded consistently to hash properly
    #
    $price = Common::RSMath::round($price, 4);
    $rate = Common::RSMath::round($rate, 4);


    $albumID = 0 unless $albumID;
    $trackID = 0 unless $trackID;

    _report("_createStatementData: "
     . $contract->artist_payee_id . ", "
     . "$payorID, "
     . $contract->artist_contract_id . ", "
     . "$albumID, "
     . "$trackID, "
     . "$productID, "
     . "$incomeSourceID, "
     . "$regionID, "
     . "$channelID, "
     . "$priceLevelID, "
     . $term->artist_contract_term_id . ", "
     . " rateTypeID=$rateTypeID "
     . "$price, $rate : sales = $sales returns = $returns revenue = $totalRevenue", 4);

    # Which of these things do I expect to always have?
    #
    assert(defined $incomeSourceID);

    $gData
    {$payorID}
    {$contract->artist_payee_id}
    {$contract->artist_contract_id}
    {$albumID}
    {$trackID}
    {$productID}
    {incomeSourceID}
    {$incomeSourceID}
    {$regionID}
    {$channelID}
    {$priceLevelID}
    {$term->artist_contract_term_id}
    {$price}
    {$rate}
    {lineitem}{sales} += $sales;

    $gData
    {$payorID}
    {$contract->artist_payee_id}
    {$contract->artist_contract_id}
    {$albumID}
    {$trackID}
    {$productID}
    {incomeSourceID}
    {$incomeSourceID}
    {$regionID}
    {$channelID}
    {$priceLevelID}
    {$term->artist_contract_term_id}
    {$price}
    {$rate}
    {lineitem}{returns} += $returns;

    $gData
    {$payorID}
    {$contract->artist_payee_id}
    {$contract->artist_contract_id}
    {$albumID}
    {$trackID}
    {$productID}
    {incomeSourceID}
    {$incomeSourceID}
    {$regionID}
    {$channelID}
    {$priceLevelID}
    {$term->artist_contract_term_id}
    {$price}
    {$rate}
    {lineitem}{revenue} += $totalRevenue;


    if ($saleID)
    {
        $gData
        {$payorID}
        {$contract->artist_payee_id}
        {$contract->artist_contract_id}
        {$albumID}
        {$trackID}
        {$productID}
        {incomeSourceID}
        {$incomeSourceID}
        {$regionID}
        {$channelID}
        {$priceLevelID}
        {$term->artist_contract_term_id}
        {$price}
        {$rate}
        {lineitem}{saleIDs}{$saleID} = 1;
    }
}

sub _getNumAlbumTracks
{
    my ($albumID) = @_;

    my $allTracks = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
    my $numTracks = $allTracks->size();
    
    return $numTracks;
}




sub _getAlbumTrackFromProduct
{
    my ($product) = @_;

    my ($albumID, $trackID);

    if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
    {   
        $trackID = $product->asset_id;
        my $trackData = RPS::DB::Item::Track->Lookup(track_id => $trackID);
        $albumID = $trackData->album_id;
    }
    else
    {   
        $albumID = $product->asset_id;
    }

    return ($albumID, $trackID);
}



       
sub _determineChannelID
{
    my ($serviceID, $formatType) = @_;

    # First, try and do an 'exact' match.
    # If that doesn't work, then we'll try using the 'wildcard' service.
    #
    my $channelID = RPS::DB::Item::ServiceFormatChannelMap->GetChannelIDByServiceAndFormat($serviceID, $formatType);
    if (! $channelID)
    {
        $channelID = RPS::DB::Item::ServiceFormatChannelMap->GetChannelIDByServiceAndFormat(RPS::DB::Item::ServiceFormatChannelMap::kAnyService, $formatType);
    }

    return $channelID;
}


sub _determineRegionID
{
    my ($countryCode) = @_;

    my $region = RPS::DB::Item::RegionCountryMap->GetRegionByCountryCode($countryCode);

    if (! $region)
    {
        return RPS::DB::Item::Region::kGlobal;
    }

    return $region->region_id;
}


# Pass dates in 'yyyy-mm-dd' format.
#
sub _parseCommandLine
{
    my ($settings) = @_;

    my %opt;
    getopts('r:c:V:', \%opt);

    if (! $opt{r} || ! $opt{c})
    {
        _usage();
        exit(1);
    }
    $settings->{runID} = $opt{r};
    $settings->{clientID} = $opt{c};

    if (defined $opt{V})
    {
        $gVerbosityLevel = $opt{V};
    }
}


sub _usage
{
    print "\nusage: $0 -r run_id [-V n]\n";
    print "\n";
    print "Arguments:\n";
    print "\t-r <run_id>\t\tThe royalty_run id to run.\n";
    print "\t-c <client_id>\t\tThe client_id this run belongs to.\n";
    print "\t-V <N>\t\t\tVerbosity level - 0 means no output\n";
}


sub _productIsDigital
{
    my ($productID) = @_;

    my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
#    my $product = _getProduct($productID);
    my $typeID = $product->product_type_id;

    if (RPS::DB::Item::Product::kProductTypeDigital == $typeID
     || RPS::DB::Item::Product::kProductTypeDigitalTrack == $typeID)
    {
        return 1;
    }

    return 0;
}

sub _applyDeduction
{
    my ($units, $deduction) = @_;

    return $units if ! $deduction;

    # calculate the deduction amount.
    # round up.
    #
    my $amountToDeduct = Common::RSMath::round($units * ($deduction / 100), 2);
    $units -= $amountToDeduct;

    return $units;
}


sub _getExpenseName
{
    my ($expense) = @_;

    my $expenseType = RPS::DB::Item::ExpenseType->Lookup(expense_type_id => $expense->expense_type_id);
    my $expenseName = RPS::DB::Item::ExpenseName->Lookup(expense_name_id => $expenseType->expense_name_id);
    return $expenseName->name;
}




sub _processReserves
{
    my ($payors) = @_;


    my %payorIDMap;
    foreach my $payorID (@$payors)
    {
        $payorIDMap{$payorID} = 1;
    }


    # Now, we need to deal with reserves that are being liquidated.
    #
    my $reserves = RPS::DB::Item::ArtistContractTermReserve->GetAllCommitted();
    while (my $reserve = $reserves->next())
    {
        _report(" reserve: " . Dumper($reserve), 3);


        # !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        # Convert this code to use the _new_ schedule scheme.
        #


        # Are we to liquidate any units this period?

        # !!! This is not really correct - need to account for both units and revenue reserves.
        #
        my $termID = $reserve->artist_contract_term_id;

        # Need to fetch the contract id, from the term.
        #
        my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $termID);
        if (! $term)
        {
            _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - term $termID cannot be found!", 3);
            next;
        }
        my $contract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $term->artist_contract_id);


        # Skip this reserve if payor_id is not in our list.
        #
        if (! $payorIDMap{ $contract->payor_id })
        {
            _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - not the right payor", 3);
            next;
        }


        # !!! So, we need to see if the Track/AlbumContract linkage still exists, and if it is still 'active'.
        #
        if ($reserve->track_id)
        {
            my $trackContract = RPS::DB::Item::TrackContract->Lookup(track_id => $reserve->track_id, artist_contract_id => $contract->artist_contract_id);
            # !!! Should I _require_ that this exist?
            if (! $trackContract)
            {
                _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - cannot find the TrackContract", 2);
                next;
            }
            if ($trackContract->status != RPS::DB::Item::TrackContract::kStatusActive)
            {
                _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - TrackContract is not active", 2);
                next;
            }
        }
        elsif ($reserve->album_id)
        {
            my $albumContract = RPS::DB::Item::AlbumContract->Lookup(album_id => $reserve->album_id, artist_contract_id => $contract->artist_contract_id);
            # !!! Should I _require_ that this exist?
            if (! $albumContract)
            {
                _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - cannot find the AlbumContract", 2);
                next;
            }
            if ($albumContract->status != RPS::DB::Item::AlbumContract::kStatusActive)
            {
                _report("skipping reserve" . $reserve->artist_contract_term_reserve_id . " - AlbumContract is not active", 2);
                next;
            }
        }


        # We've determined that this reserve 'belongs' to this run, so mark it.
        #
        my $runMapItem = RPS::DB::Item::ArtistContractTermReserveRun->Create
        (
            artist_contract_term_reserve_id => $reserve->artist_contract_term_reserve_id,
            artist_royalty_run_id => $gRunID,
        );
        $runMapItem->save();


        # If the period == 1, we liquidate
        #
        my $period = $reserve->periods_remaining;
        if (1 != $period)
        {
            next;
        }

        # Figure out whether we have a single album contract, or a list of track contracts.
        # We'll need to know the track ids and the proration amount.
        #
#        my $trackProrationData = _getTrackProrationData($reserve->product_id, $term);


        my $product = RPS::DB::Item::Product->Lookup(product_id => $reserve->product_id);
        if (! $product)
        {
            die "ERROR - reserve references a non-existent product: " . Dumper($reserve) . "\n";
        }
        if ($reserve->revenue_based)
        {
            my $reserveRevenue = $reserve->revenue;

            if ($reserveRevenue)
            {
                # Fetch the contract term and the contract, so we can get the
                # rest of the relevant info.
                #
#                my $albumID = $product->album_id;
#                my $albumID = _getAlbumIDFromProduct($product);
                my $albumID = $reserve->album_id;
                my $productID = $reserve->product_id;
                my $incomeSourceID = $reserve->income_source_id;
                my $regionID = $reserve->region_id;
                my $channelID = $reserve->channel_id;
                my $priceLevelID = $reserve->price_level_id;


                # Price is meaningless unless this is a percent average term.
                # Force it to '0' so everything will hash correctly.
                #
                my $rateTypeID = $term->contract_rate_type_id;
                my $price = $reserve->price;


                # !!! I want to trust the price and price level unless for some reason price is 0...
                #
                if (0 == $price)
                {
                    ($price, $priceLevelID) = _determinePrice($rateTypeID, $price, $priceLevelID, $productID);
                }

                my $clientID = Common::RSApp::GetClientID();
                if (123 == $clientID) # WELK
                {
                    if (0 == $price)
                    {
                        if (RPS::DB::Item::ContractRateType::kRateTypePercentRetail == $rateTypeID
                         || RPS::DB::Item::ContractRateType::kRateTypePercentWholesale == $rateTypeID
                         || RPS::DB::Item::ContractRateType::kRateTypePercentPPD == $rateTypeID)
                        {
                            # un-mark this reserve!
                            #
                            _report("SKIPSKIPSKIP : Skipping reserve " . $reserve->artist_contract_term_reserve_id . " product:$productID");

                            # Delete the entry from the map table, so we don't touch it.
                            #
                            $runMapItem->delete();
                            next;
                        }
                    }
                }

                # !!!!!! We should _keep_ price - But, perhaps if price is 0 then we need to determine it from
                # !!!!!! the price level id.
                #
#                $price = 0 unless $price > 0 && RPS::DB::Item::ContractRateType::kRateTypePercentAverage == $rateTypeID;


#                foreach my $trackProration (@$trackProrationData)
#                {
#                    my $trackID = $trackProration->{track_id};
                    my $trackID = $reserve->track_id;

                    $trackID = 0 unless $trackID;

                    # Round the rate, to make sure we are always hashing on the same things
                    # (i.e. 40 vs 40.0000)
                    #

                    # !!! My theory now is that this proration is not necessary.
                    # !!! It seems to be getting applied more that is necessary.
                    # !!! In other words, every time we liquidate reserves we reduce _again_!
                    # 
#                    my $count = $trackProration->{proration_count};
                    my $count = 1;
                    if (! $count)
                    {
                        _report("WARNING - 0 proration count");
                        $count = 1;
                    }
                    my $rate = Common::RSMath::round($reserve->effective_rate / $count, 4);

                    # !!! price and rate will need to be rounded consistently to hash properly
                    #
                    $price = Common::RSMath::round($price, 4);
                    $rate = Common::RSMath::round($rate, 4);

                    $gData
                        {$contract->payor_id}
                        {$contract->artist_payee_id}
                        {$term->artist_contract_id}
                        {$albumID}
                        {$trackID}
                        {$productID}
                        {incomeSourceID}
                        {$incomeSourceID}
                        {$regionID}
                        {$channelID}
                        {$priceLevelID}
                        {$term->artist_contract_term_id}
                        {$price}
                        {$rate}
                        {lineitem}{revenueLiquidated} += $reserveRevenue;
#                }
            }
        }
        else
        {
            my $reserveUnits = $reserve->units;

            if ($reserveUnits)
            {
                # Fetch the contract term and the contract, so we can get the
                # rest of the relevant info.
                #
#                my $albumID = _getAlbumIDFromProduct($product);
#                my $albumID = $product->album_id;
                my $albumID = $reserve->album_id;
                my $productID = $reserve->product_id;
                my $incomeSourceID = $reserve->income_source_id;
                my $regionID = $reserve->region_id;
                my $channelID = $reserve->channel_id;
                my $priceLevelID = $reserve->price_level_id;


                my $rateTypeID = $term->contract_rate_type_id;
                my $price = $reserve->price;

                # !!! I want to trust the price and price level unless for some reason price is 0...
                #
                if (0 == $price)
                {
                    ($price, $priceLevelID) = _determinePrice($rateTypeID, $price, $priceLevelID, $productID);
                }

                my $clientID = Common::RSApp::GetClientID();
                if (123 == $clientID) # WELK
                {
                    if (0 == $price)
                    {
                        if (RPS::DB::Item::ContractRateType::kRateTypePercentRetail == $rateTypeID
                         || RPS::DB::Item::ContractRateType::kRateTypePercentWholesale == $rateTypeID
                         || RPS::DB::Item::ContractRateType::kRateTypePercentPPD == $rateTypeID)
                        {
                            # un-mark this reserve!
                            #
                            _report("SKIPSKIPSKIP : Skipping reserve " . $reserve->artist_contract_term_reserve_id . " product:$productID");

                            # Delete the entry from the map table, so we don't touch it.
                            #
                            $runMapItem->delete();
                            next;
                        }
                    }
                }
                # !!! Not sure WHY we are setting this to 0.  Doesn't seem to help.
                # !!! So I am going to comment it out.
#                $price = 0 unless $price > 0 && RPS::DB::Item::ContractRateType::kRateTypePercentAverage == $rateTypeID;



                # !!! The big question: Why am I doing track proration here?
                # !!! Haven't we _already_ prorated this?

#                foreach my $trackProration (@$trackProrationData)
#                {
#                    my $trackID = $trackProration->{track_id};
                    my $trackID = $reserve->track_id;
                    $trackID = 0 unless $trackID;

                    # Round the rate, to make sure we are always hashing on the same things
                    # (i.e. 40 vs 40.0000)
                    #
#                    my $count = $trackProration->{proration_count};
                    my $count = 1;
                    if (! $count)
                    {
                        _report("WARNING - 0 proration count");
                        $count = 1;
                    }
                    my $rate = Common::RSMath::round($reserve->effective_rate / $count, 4);

                    # !!! price and rate will need to be rounded consistently to hash properly
                    #
                    $price = Common::RSMath::round($price, 4);
                    $rate = Common::RSMath::round($rate, 4);

                    _report(" price $price  rate $rate", 3);
                    $gData
                        {$contract->payor_id}
                        {$contract->artist_payee_id}
                        {$term->artist_contract_id}
                        {$albumID}
                        {$trackID}
                        {$productID}
                        {incomeSourceID}
                        {$incomeSourceID}
                        {$regionID}
                        {$channelID}
                        {$priceLevelID}
                        {$term->artist_contract_term_id}
                        {$price}
                        {$rate}
                        {lineitem}{unitsLiquidated} += $reserveUnits;
#                }
            }
        }
    }
}

sub _determineIncomeSourceID
{
    my ($productType, $formatType) = @_;
    my $incomeSourceID;
    $formatType = uc($formatType);
    $productType = uc($productType);

    if ('D' eq $formatType)
    {
        if ('T' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrack;
        }
        elsif ('A' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbum;
        }

    }
    elsif ('H' eq $formatType)
    {
        if ('T' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackPremium;
        }
        elsif ('A' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumPremium;
        }

    }
    elsif ('I' eq $formatType)
    {
        if ('T' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackUpgrade;
        }
        elsif ('A' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumUpgrade;
        }

    }
    elsif ('S' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalStream;
    }
    elsif ('T' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTethered;
    }
    elsif ('R' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceRingtone;
    }
    elsif ('B' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceBackground;
    }
    elsif ('P' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceVPD;
    }
    elsif ('E' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDualDownload;
    }
    elsif ('J' eq $formatType)
    {
        $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceJukebox;
    }
    else
    {
        if ('2' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceCD;
        }
        elsif ('1' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceLP;
        }
        elsif ('4' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceCassette;
        }
        elsif ('M' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceMasterLicense;
        }
        elsif ('S' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceSyncLicense;
        }
        elsif ('9' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDVD;
        }
        elsif ('D' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDVDCDSet;
        }
        elsif ('3' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceVHS;
        }
        elsif ('B' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceDblCD;
        }
        elsif ('C' eq $productType)
        {
            $incomeSourceID = RPS::DB::Item::IncomeSource::kIncomeSourceCDSingle;
        }
        else
        {
            warn "ERROR - unable to determine income source id from productType $productType formatType $formatType";
        }

    }

    # !!! Add support for VPD when we have choosen codes.

    return $incomeSourceID;
}


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

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


my $productTypeTable;
sub _canTakeReserves
{
    my ($productID) = @_;

    return 0 unless $productID;

    
    if (! $productTypeTable)
    {
        $productTypeTable = {};
        my $productTypes = RPS::DB::Item::ProductType->GetAll();
        while (my $pt = $productTypes->next())
        {
            $productTypeTable->{$pt->product_type_id} = $pt->type;
        }
    }

    my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);

    if (RPS::DB::Item::ProductType::kTypePhysical == $productTypeTable->{$product->product_type_id} )
    {
        return 1;
    }
    return 0;
}


sub _buildCrossTable
{
    my $albumContracts = RPS::DB::Item::AlbumContract->GetCrossCollateralized();
    while (my $albumContract = $albumContracts->next())
    {
        next unless RPS::DB::Item::AlbumContract::kStatusActive == $albumContract->status();

        my $artistContract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $albumContract->artist_contract_id);
        $gCrossed{$artistContract->artist_payee_id}{$albumContract->album_id}{$artistContract->artist_contract_id} = 1;
    }

    my $trackContracts = RPS::DB::Item::TrackContract->GetCrossCollateralized();
    while (my $trackContract = $trackContracts->next())
    {
        next unless RPS::DB::Item::TrackContract::kStatusActive == $trackContract->status();

        my $track = RPS::DB::Item::Track->Lookup(track_id => $trackContract->track_id);
        my $artistContract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $trackContract->artist_contract_id);
        $gCrossed{$artistContract->artist_payee_id}{$track->album_id}{$artistContract->artist_contract_id} = 1;
    }
}

sub _subtractStatRateFromNetRevenue
{
    my $clientInfo = Common::DB::Item::Client->Lookup(client_id => Common::RSApp::GetClientID());
    return $clientInfo->subtract_stat_rate_from_net_revenue;
}

sub _determineStatRatePrice
{
    my ($sale) = @_;

    my $saleStatRateID = _getStatRateID($sale->date_end);
    my ($rate, $minuteRate) = _getStatRate($saleStatRateID);


    my $product = RPS::DB::Item::Product->Lookup(product_id => $sale->product_id);


    # Get the tracks on this product.
    #
    my $productTracks = _getTracksFromProduct($product);


    # Calculate the amount of stat rate that would have been paid on all the tracks
    # associated with this sale.  We skip bonus tracks, since we don't pay mechanicals on those.
    #
    my $amount = 0;
    foreach my $productTrack (@$productTracks)
    {
        next if (RPS::DB::Item::ProductTrack->TrackIsBonusTrack($productTrack->track_id));

        my $track = RPS::DB::Item::Track->Lookup(track_id => $productTrack->track_id);

        my $masterID = $track->master_id;
        croak "!!! Track has no master, cannot determine duration" unless $masterID;
        my $masterData = RPS::DB::Item::Master->Lookup(master_id => $masterID);
        my $duration = $masterData->duration;

        my $effectiveRate = $rate;
        if ($duration > 300)
        {
            my $minutes = ceil($duration / 60);
            my $minBasedRate = ($minutes * $minuteRate);

            $effectiveRate = ($minBasedRate > $effectiveRate ? $minBasedRate : $effectiveRate);
        }

        $amount += $effectiveRate;
    }

    _report("for product " . $sale->product_id . ", effective stat rate for sale = $amount", 3);

    return $amount;
}

sub _getTracksFromProduct
{
    my ($product) = @_;

    my @tracks;

    if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
    {   
        my $trackID = $product->asset_id;
        my $trackDBItem = _getTrack($trackID);
        if (! $trackDBItem->mechanical_exempt)
        {   
            push @tracks, $trackDBItem;
        }
    }
#    elsif ($product->album_id)
    else
    {   
        # (XXX) Make sure this interface takes bonus tracks into account.
        # jpk - seems to.
        #
        my $collection = RPS::DB::Item::ProductTrack->GetTracksByProductID($product->product_id);
        while ($collection->hasNext())
        {   
            my $trackDBItem = $collection->next();
            my $track = _getTrack($trackDBItem->track_id);
            if (! $track->mechanical_exempt)
            {   
                push @tracks, $trackDBItem;
            }
        }
    }

    return \@tracks;
}




my $gTrackMap;
sub _getTrack
{
    my ($trackID) = @_;

    if (! $gTrackMap)
    {
        _report("Caching all tracks...", 2);

        $gTrackMap = {};
        my $trackList = RPS::DB::Item::Track->GetAll();
        while (my $track = $trackList->next())
        {
            $gTrackMap->{$track->track_id} = $track;
        }
    }
    return $gTrackMap->{$trackID};
}


my $gStatRateTable;
my $gStatRateByDate;
sub _getStatRateTable
{
    my $timer = Common::Timer->new();
    if (! $gStatRateTable)
    {
        $gStatRateTable = {};

        my $statRates = RPS::DB::Item::StatRate->GetAll();
        while (my $statRate = $statRates->next())
        {
            $gStatRateTable->{$statRate->stat_rate_id} = $statRate;
        }
    }

    return $gStatRateTable;
}


# Generally we are going to be asking for the same stat rate for the same
# date over and over and over and over... so it makes sense to cache it.
#
sub _getStatRateID
{
    my $endDate = shift;
    my $timer = Common::Timer->new();

    if (! $gStatRateByDate->{$endDate})
    {
        my $table = _getStatRateTable();

        # get the stat rates, sorted by date
        #
        my @sortedRateIDs = sort { $table->{$b}->date_effective <=> $table->{$a}->date_effective} keys %$table;
        foreach my $testRateID (@sortedRateIDs)
        {
            if ($table->{$testRateID}->date_effective <= $endDate)
            {
                $gStatRateByDate->{$endDate} = $table->{$testRateID}->stat_rate_id;
                last;
            }
        }
    }
    return $gStatRateByDate->{$endDate};
}


sub _getStatRate
{
    my $statRateID = shift;
    my $timer = Common::Timer->new();
    
    my $table = _getStatRateTable();
    my $statRate = $table->{$statRateID};
    assert($statRate);

    return ($statRate->rate, $statRate->minute_rate);
}

sub _isAlbumIncomeSource
{
    my ($incomeSourceID) = @_;
    if (RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbum == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceCD == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceLP == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceCassette == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceCDSingle == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDblCD == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumPremium == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumUpgrade == $incomeSourceID)
    {
        return 1;
    }

    return 0;
}

sub _isDownload
{
    my ($incomeSourceID) = @_;
    if (RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbum == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrack == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumPremium == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackPremium == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalAlbumPremium == $incomeSourceID
     || RPS::DB::Item::IncomeSource::kIncomeSourceDigitalTrackPremium == $incomeSourceID
    )
    {
        return 1;
    }

    return 0;
}

sub _getTrackProrationData
{
    my ($productID, $term) = @_;


    my $artistContractID = $term->artist_contract_id;
    my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);

    # !!! ASSUMPTION - We only reserve physical cd sales.  So we don't need to think about track products...
    #
    assert(RPS::DB::Item::Product::kProductTypeDigitalTrack != $product->product_type_id);
    my $albumID = $product->asset_id;


    my @results;

    # Is there an album contract?
    #
    my $albumContract = RPS::DB::Item::AlbumContract->GetByContractIDAlbumID($artistContractID, $albumID);
    if ($albumContract && RPS::DB::Item::AlbumContract::kStatusActive == $albumContract->status())
    {   
        # This code doesn't make a lot of sense...
        # There would have never been a track_id.
        # It seems to work, though, so will makeit more explicit
        # push @results, { track_id => $product->track_id, proration_count => 1 };
        push @results, { track_id => undef, proration_count => 1 };
    }
    else
    {   
        # There had better be some track contracts...
        #
        my $trackContracts = RPS::DB::Item::TrackContract->GetByContractIDAlbumID($artistContractID, $albumID);
        while (my $trackContract = $trackContracts->next())
        {   
            next unless RPS::DB::Item::TrackContract::kStatusActive == $trackContract->status();

            my $trackID = $trackContract->track_id;
            my $count = $trackContract->prorate_track_count;
            if (! $count)
            {   
                _report("WARNING - no prorate_track_count: " . Dumper($trackContract), 2);
                $count = 1;
            }
            push @results, { track_id => $trackID, proration_count => $count };
        }
    }


    return \@results;
}



my %gFileDistFeeMap;
sub _getDistributionFeeFromSale
{
    my ($sale) = @_;

    if (! defined $gFileDistFeeMap{$sale->file_id})
    {
        $gFileDistFeeMap{$sale->file_id} = RPS::DB::Item::DistributionFee->GetDistFeeBySale(file_id => $sale->file_id, sale_id => $sale->sale_id);
    }
    return $gFileDistFeeMap{$sale->file_id};
}


sub _getArtistPayeeBalances
{
    my %resultHash;

    my $allAccountMappings = RPS::DB::Item::ArtistPayeeAccount->GetAll();
    while (my $accountMap = $allAccountMappings->next())
    {
        # Get the current balance for this account.
        # !!! I'd like to make an entry in this hash if this guy has _pending transactions_, too.
        #
        my $currentBalance;
        my $hasPendingTransactions;
        my $pendingTransactions;
        if ($accountMap->finance_account_id)
        {
            $currentBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($accountMap->finance_account_id);
            $pendingTransactions = RPS::DB::Item::PendingTransaction->GetAccountTransactions(finance_account_id => $accountMap->finance_account_id);
        }
        if ($currentBalance != 0 || ($pendingTransactions && $pendingTransactions->size() > 0))
        {
            $resultHash{$accountMap->payor_id}{$accountMap->artist_payee_id}{balance} = $currentBalance;
            if ($pendingTransactions && $pendingTransactions->size() > 0)
            {
                $resultHash{$accountMap->payor_id}{$accountMap->artist_payee_id}{pending} = $pendingTransactions;
            }
        }
    }
    _report("_getArtistPayeeBalances: " . Dumper(\%resultHash), 4);
    return \%resultHash;
}

sub _getAlbumBalances
{
    my %resultHash;

    my $accountMap = RPS::DB::Item::ArtistRoyaltyAlbumBalanceAccount->GetAll();
    while (my $mapping = $accountMap->next())
    {
        # I only need to concern myself with non-0 balances, right?
        #
        my $currentBalance = RPS::DB::Item::FinanceAccount->CurrentBalance($mapping->account_id);
        if ($currentBalance != 0)
        {
            $resultHash{$mapping->payor_id}{$mapping->artist_payee_id}{$mapping->album_id}{$mapping->artist_contract_id} = $currentBalance;
        }
    }
    _report("_getAlbumBalances: " . Dumper(\%resultHash), 4);
    return \%resultHash;
}

sub _getAlbumPreviousBalance
{
    my ($payorID, $artistPayeeID, $albumID, $artistContractID) = @_;

    my $balance = 0;
    my $accountMap = RPS::DB::Item::ArtistRoyaltyAlbumBalanceAccount->Lookup
    (
        album_id => $albumID, 
        artist_payee_id => $artistPayeeID,
        payor_id => $payorID,
        artist_contract_id => $artistContractID,
    );
    if ($accountMap)
    {
        my $accountID = $accountMap->account_id;
        $balance = RPS::DB::Item::FinanceAccount->CurrentBalance($accountID);
    }

    return $balance;
}

sub _getAlbumIDFromProduct
{
    my ($product) = @_;

    if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
    {
        my $trackID = $product->asset_id;
        my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID);
        return $track->album_id;
    }
    else
    {
        return $product->asset_id;
    }
}

sub _getAllPayorIDs
{
    my @ids;
    my $payors = RPS::DB::Item::Payor->GetAll();
    while (my $payor = $payors->next())
    {
        push @ids, $payor->payor_id;
    }

    return \@ids;
}


sub _createUnitReserve
{
    my ($incomeItem, $productID) = @_;

    my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $incomeItem->artist_contract_term_id);

    my $units = $incomeItem->units_reserved;

    my %scheduleTable;
    my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType($term->artist_contract_id, RPS::DB::Item::ReserveLiquidation::kTypeArtistContract);

    while ($schedule->hasNext())
    {
        my $scheduleEntry = $schedule->next();
        $scheduleTable{$scheduleEntry->period} = $scheduleEntry->percent;
    }

    my $albumID = $incomeItem->album_id;
    my $trackID = $incomeItem->track_id;
    $trackID = 0 unless $trackID;


    my $initialUnits = $units;
    for (my $i = 1; $units > 0 && $i <= 8; $i++)
    {
        my $p = $scheduleTable{$i};
        next unless $p;

        my $periodUnits = ceil($initialUnits * ($p / 100));
        $periodUnits = $units unless $periodUnits < $units;
        $units -= $periodUnits;

        my $newReserve = RPS::DB::Item::ArtistContractTermReserve->Create
        (
            artist_contract_term_id     => $incomeItem->artist_contract_term_id,
            original_statement_item_id  => $incomeItem->artist_royalty_income_item_id,
            income_source_id            => $incomeItem->income_source_id,
            region_id                   => $incomeItem->region_id,
            channel_id                  => $incomeItem->channel_id,
            price_level_id              => $incomeItem->price_level_id,
            product_id                  => $productID,
            units                       => $periodUnits,
            revenue_based               => 0,
            effective_rate              => $incomeItem->rate,  # This column should be called 'base_rate'...
            #net_rate              			=> $incomeItem->net_rate,  # This column should be called 'effective_rate'...
            price                       => $incomeItem->price,
            periods_remaining           => $i + 1,
            album_id                    => $albumID,
            track_id                    => $trackID,
        );

        $newReserve->save();

        my $runMapItem = RPS::DB::Item::ArtistContractTermReserveRun->Create
        (
            artist_contract_term_reserve_id => $newReserve->artist_contract_term_reserve_id,
            artist_royalty_run_id => $gRunID,
        );
        $runMapItem->save();
    }
}

sub _createRevenueReserve
{
    my ($incomeItem, $productID) = @_;


    my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $incomeItem->artist_contract_term_id);
    my $revenue = $incomeItem->revenue_reserved;

    my %scheduleTable;
    my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType($term->artist_contract_id, RPS::DB::Item::ReserveLiquidation::kTypeArtistContract);

    my $numPeriods = 0;
    while ($schedule->hasNext())
    {
        my $scheduleEntry = $schedule->next();
        $scheduleTable{$scheduleEntry->period} = $scheduleEntry->percent;
        if ($scheduleEntry->percent > 0)
        {
            $numPeriods++;
        }
    }

    my $albumID = $incomeItem->album_id;
    my $trackID = $incomeItem->track_id;
    $trackID = 0 unless $trackID;


    my $initialRevenue = $revenue;
    for (my $i = 1; $revenue > 0 && $i <= 8; $i++)
    {
        my $p = $scheduleTable{$i};
        next unless $p;

        my $periodRevenue = Common::RSMath::round($initialRevenue * ($p / 100), 2);
        $periodRevenue = $revenue unless ($periodRevenue < $revenue && $numPeriods > 1);
        $revenue -= $periodRevenue;
        $numPeriods--;


        my $newReserve = RPS::DB::Item::ArtistContractTermReserve->Create
        (
            artist_contract_term_id     => $incomeItem->artist_contract_term_id,
            original_statement_item_id  => $incomeItem->artist_royalty_income_item_id,
            income_source_id            => $incomeItem->income_source_id,
            region_id                   => $incomeItem->region_id,
            channel_id                  => $incomeItem->channel_id,
            price_level_id              => $incomeItem->price_level_id,
            product_id                  => $productID,
            revenue                     => $periodRevenue,
            revenue_based               => 1,
            effective_rate              => $incomeItem->rate,  # This column should be called 'base_rate'...
            #net_rate              			=> $incomeItem->net_rate,  # This column should be called 'effective_rate'...
            price                       => $incomeItem->price,
            periods_remaining           => $i+1,
            album_id                    => $albumID,
            track_id                    => $trackID,
        );

        $newReserve->save();
        my $runMapItem = RPS::DB::Item::ArtistContractTermReserveRun->Create
        (
            artist_contract_term_reserve_id => $newReserve->artist_contract_term_reserve_id,
            artist_royalty_run_id => $gRunID,
        );
        $runMapItem->save();
    }
}


sub _determinePrice
{
    my ($rateType, $averagePrice, $priceLevelID, $productID) = @_;

    my ($price, $actualPriceLevelID);

    if (RPS::DB::Item::ContractRateType::kRateTypePercentRevenue == $rateType)
    {
        # price is meaningless for net revenue rates.
        # So... do nothing.
    }
    elsif(RPS::DB::Item::ContractRateType::kRateTypePercentRetail == $rateType)
    {
        ($price, $actualPriceLevelID) = _getRetailPrice($priceLevelID, $productID);
    }
    elsif (RPS::DB::Item::ContractRateType::kRateTypePercentWholesale == $rateType)
    {
        ($price, $actualPriceLevelID) = _getWholesalePrice($priceLevelID, $productID);
    }
    elsif (RPS::DB::Item::ContractRateType::kRateTypePercentPPD == $rateType)
    {
        ($price, $actualPriceLevelID) = _getPPDPrice($priceLevelID, $productID);
    }
    elsif (RPS::DB::Item::ContractRateType::kRateTypePercentAverage == $rateType)
    {
        $price = $averagePrice;
    }
    elsif (RPS::DB::Item::ContractRateType::kRateTypeFixed == $rateType)
    {
        # This is a little different.
        # The price is stored in the rate column.
        # In other words, the base rate _is_ the price.
        # We'll leave it there, so it shows up on the statement
        # in the 'rate' column.
        #
        $price = 1;

        # I think the base rate, though, is not a percentage, but a real rate.
        # This means we want to _not_ divide the rate by 100 during our
        # normal calculations.
    }

    return ($price, $actualPriceLevelID);
}

sub _logMissedSale
{
    my ($sale, $why, %args) = @_;

# product, trackID, albumID, contract

    # Get as much state as we can out of the sale.  Some of this information
    # may be missing (which could explain why the sale was skipped...)
    #
    my $newLogEntry = RPS::DB::Item::ArtistRoyaltyRunMissedSaleLog->Create();

    $newLogEntry->artist_royalty_run_id($gRunID);
    $newLogEntry->sale_id($sale->sale_id);
    $newLogEntry->reason($why);

    if ($args{productID})
    {
        my $productID = $args{productID};
        my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
        if ($product)
        {
            $newLogEntry->product_id($productID);
            $newLogEntry->product_type_id($product->product_type_id());
            $newLogEntry->product_type(_idToProductType($product->product_type_id()));
        }
    }

    my $artistID;
    if ($args{albumID})
    {
        my $albumID = $args{albumID};
        my $album = RPS::DB::Item::Album->Lookup(album_id => $albumID);
        if ($album)
        {
            $newLogEntry->album_id($albumID);
            $newLogEntry->album_title($album->title());
            $newLogEntry->catalog_number($album->catalog_number());

            $artistID = $album->artist_id;
        }
    }

    if ($args{trackID})
    {
        my $trackID = $args{trackID};
        my $track = RPS::DB::Item::Track->Lookup(track_id => $trackID);
        if ($track)
        {
            $newLogEntry->track_id($trackID);
            $newLogEntry->track_title($track->title());
            $artistID = $track->artist_id;
        }
    }

    if ($args{contract})
    {
        my $contractID = $args{contract}->artist_contract_id;
        $newLogEntry->artist_contract_id($contractID);
        $newLogEntry->artist_contract_title($args{contract}->title);
    }

    if ($args{details})
    {
        $newLogEntry->details($args{details});
    }

    if ($artistID)
    {
        my $artist = RPS::DB::Item::Artist->Lookup(artist_id => $artistID);
        if ($artist)
        {
            $newLogEntry->artist_name($artist->name);
        }
    }

    # !!! Why do we really need to show this crap?
    #
    my $units = $sale->units();
    if (! $units)
    {
        $units = $sale->sales() - $sale->returns();
    }
    $newLogEntry->units($units);
    $newLogEntry->total_revenue($sale->total_revenue());

    
    $newLogEntry->save();
}

my $gChannelIDMap;
sub _idToChannel
{
    my ($id) = @_;

    if (! $gChannelIDMap)
    {
        $gChannelIDMap = {};

        my $allChannels = RPS::DB::Item::Channel->GetAll();
        while (my $is = $allChannels->next())
        {
            $gChannelIDMap->{$is->channel_id} = $is->name;
        }
    }
    my $channelName = '(Missing)';
    
    if ($gChannelIDMap->{$id})
    {
        $channelName = $gChannelIDMap->{$id};
    }

    return $channelName;
}

my $gIncomeSourceIDMap;
sub _idToIncomeSource
{
    my ($id) = @_;

    if (! $gIncomeSourceIDMap)
    {
        $gIncomeSourceIDMap = {};

        my $allIncomeSources = RPS::DB::Item::IncomeSource->GetAll();
        while (my $is = $allIncomeSources->next())
        {
            $gIncomeSourceIDMap->{$is->income_source_id} = $is->name;
        }
    }
    
    my $incomeSourceName = '(Missing)';
    
    if ($gIncomeSourceIDMap->{$id})
    {
        $incomeSourceName = $gIncomeSourceIDMap->{$id};
    }

    return $incomeSourceName;     
}

my $gPriceLevelIDMap;
sub _idToPriceLevel
{
    my ($id) = @_;

    if (! $gPriceLevelIDMap)
    {
        $gPriceLevelIDMap= {};

        my $all = RPS::DB::Item::PriceLevel->GetAll();
        while (my $pl = $all->next())
        {
            $gPriceLevelIDMap->{$pl->price_level_id} = $pl->name;
        }
    }
    
    my $priceLevelName = '(Missing)';
    
    if ($gPriceLevelIDMap->{$id})
    {
        $priceLevelName = $gPriceLevelIDMap->{$id};
    }

    return $priceLevelName;    
}


my $gProductTypeIDMap;
sub _idToProductType
{
    my ($id) = @_;

    if (! $gProductTypeIDMap)
    {
        $gProductTypeIDMap = {};

        my $all = RPS::DB::Item::ProductType->GetAll();
        while (my $pt = $all->next())
        {
            $gProductTypeIDMap->{$pt->product_type_id} = $pt->description;
        }
    }

    my $productTypeName = '(Missing)';
    
    if ($gProductTypeIDMap->{$id})
    {
        $productTypeName = $gProductTypeIDMap->{$id};
    }

    return $productTypeName;        
}


my $gSaleProductTypeIDMap;
sub _idToSaleProductType
{
    my ($id) = @_;

    if (! $gSaleProductTypeIDMap)
    {
        $gSaleProductTypeIDMap = {};

        my $all = RPS::DB::Item::SaleProductType->GetAll();
        while (my $pt = $all->next())
        {
            $gSaleProductTypeIDMap->{$pt->sale_product_type_id} = $pt->description;
        }
    }

    my $saleProductTypeName = '(Missing)';
    
    if ($gSaleProductTypeIDMap->{$id})
    {
        $saleProductTypeName = $gSaleProductTypeIDMap->{$id};
    }

    return $saleProductTypeName;        
}


my $gFormatTypeIDMap;
sub _idToFormatType
{
    my ($id) = @_;

    if (! $gFormatTypeIDMap)
    {
        $gFormatTypeIDMap = {};

        my $all = RPS::DB::Item::Format->GetAll();
        while (my $ft = $all->next())
        {
            $gFormatTypeIDMap->{$ft->format_type} = $ft->format_name;
        }
    }

    my $formatTypeName = '(Missing)';
    
    if ($gFormatTypeIDMap->{$id})
    {
        $formatTypeName = $gFormatTypeIDMap->{$id};
    }

    return $formatTypeName; 
}


sub _createArtistReservePipelineReport
{
    my %gPipeline;

    my $runReserves= RPS::DB::Item::ArtistContractTermReserveRun->GetByArtistRoyaltyRunID($gRunID);
    while (my $runReserveItem = $runReserves->next())
    {
        my $reserveID = $runReserveItem->artist_contract_term_reserve_id;
        my $reserve = RPS::DB::Item::ArtistContractTermReserve->Lookup(artist_contract_term_reserve_id => $reserveID);
        my $productID = $reserve->product_id;
        my $product = RPS::DB::Item::Product->Lookup(product_id => $productID);
        my $albumID = _getAlbumIDFromProduct($product);



        my $termID = $reserve->artist_contract_term_id;

        my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $termID);
        my $artistContractID = $term->artist_contract_id;
        my $artistContract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $artistContractID);
        my $artistPayeeID = $artistContract->artist_payee_id;

        if ($reserve->revenue_based)
        {
            $gPipeline{$artistPayeeID}{$albumID}{totalRevenue} += $reserve->remaining_revenue;
        }
        else
        {
            $gPipeline{$artistPayeeID}{$albumID}{totalUnits} += $reserve->remaining_units;
        }


        my $liquidationSchedule = RPS::DB::Item::ReserveLiquidation->GetByIDType($artistContractID, 
         RPS::DB::Item::ReserveLiquidation::kTypeArtistContract);

        my $currentPeriod = $reserve->next_period;


        # Note - the schedule _is_ ordered by period.
        #
        my $remainingRevenue = $reserve->remaining_revenue;
        my $remainingUnits = $reserve->remaining_units;

        while (my $liq = $liquidationSchedule->next())
        {
            my $periodDelta = $liq->period - $currentPeriod;

            # Skip liquidation schedule entries that have already passed.
            #
            next if ($periodDelta < 0);


            if ($reserve->revenue_based)
            {
                my $reserveRevenue = ceil($reserve->initial_revenue * ($liq->percent / 100));

                if ($reserveRevenue > $remainingRevenue)
                {
                    $reserveRevenue = $remainingRevenue;
                }

                $remainingRevenue -= $reserveRevenue;
                $gPipeline{$artistPayeeID}{$albumID}{$periodDelta}{revenue} += $reserveRevenue;

                last if (! $remainingRevenue);
            }
            else
            {
                my $reserveUnits = ceil($reserve->initial_units * ($liq->percent / 100));

                if ($reserveUnits > $remainingUnits)
                {
                    $reserveUnits = $remainingUnits;
                }
                $remainingUnits -= $reserveUnits;

                $gPipeline{$artistPayeeID}{$albumID}{$periodDelta}{units} += $reserveUnits;

                last if (! $remainingUnits);
            }
        }
    }

    # Write the data out to the table
    #
    foreach my $artistPayeeID (keys %gPipeline)
    {
        my $albumIDHash = $gPipeline{$artistPayeeID};
        foreach my $albumID (keys %$albumIDHash)
        {
            my $data = $albumIDHash->{$albumID};

            my $insertSQL = "INSERT INTO artist_reserve_pipeline SET "
             . 'artist_royalty_run_id=?,'
             . 'artist_payee_id=?,'
             . 'album_id=?,'
             . 'total_revenue=?,'
             . 'total_units=?,'
             . 'revenue_0=?,'
             . 'units_0=?,'
             . 'revenue_1=?,'
             . 'units_1=?,'
             . 'revenue_2=?,'
             . 'units_2=?,'
             . 'revenue_3=?,'
             . 'units_3=?,'
             . 'revenue_4=?,'
             . 'units_4=?,'
             . 'revenue_5=?,'
             . 'units_5=?,'
             . 'revenue_6=?,'
             . 'units_6=?,'
             . 'revenue_7=?,'
             . 'units_7=?,'
             . 'revenue_8=?,'
             . 'units_8=?';

            my $dbo = Common::RSApp::GetClientDB();
            $dbo->DoCmdWithPlaceholders($insertSQL,
            [
                $gRunID,
                $artistPayeeID,
                $albumID,
                $data->{totalRevenue},
                $data->{totalUnits},
                $data->{0}{revenue},
                $data->{0}{units},
                $data->{1}{revenue},
                $data->{1}{units},
                $data->{2}{revenue},
                $data->{2}{units},
                $data->{3}{revenue},
                $data->{3}{units},
                $data->{4}{revenue},
                $data->{4}{units},
                $data->{5}{revenue},
                $data->{5}{units},
                $data->{6}{revenue},
                $data->{6}{units},
                $data->{7}{revenue},
                $data->{7}{units},
                $data->{8}{revenue},
                $data->{8}{units}
            ]
            );
        }
    }


}
        
sub _getPaidContractIDsForSale
{
    my ($sale) = @_;

    my %contractHash;

    my $saleID = $sale->sale_id;

    if (RPS::File::Sale::TYPE_LICENSE_INCOME eq $sale->product_type)
    {
        my $mapItems = RPS::DB::Item::SaleRunMap->GetPaidLicenseIncomeArtistRoyaltyBySaleID($saleID);
        while (my $mapItem = $mapItems->next())
        {
            my $trackID = 0;
            my $contractID = 0;

            # Not all of these entries will _have_ a statement item id.
            # Data we pulled from the old sale_artist_royalty_run_map table, for example.
            #
            next unless $mapItem->statement_item_id;

            my $originalLicenseIncomeItem = RPS::DB::Item::ArtistRoyaltyLicenseIncomeItem->Lookup(artist_royalty_license_income_item_id => $mapItem->statement_item_id);
            if (! $originalLicenseIncomeItem)
            {
                _report("ERROR - cannot find original license income item for sale map item: " . Dumper($mapItem));
                next;
            }

            # We want to determine the _track_ that was associated with this payment, if any.
            # This is because in theory the same contract can be attached to multiple tracks on
            # a single album.  So, we want to make sure we are able to distinguish between new
            # track-contracts and old track-contracts for album level sales.
            #
            if ($originalLicenseIncomeItem->track_id)
            {
                $trackID = $originalLicenseIncomeItem->track_id;
            }
            $contractID = $originalLicenseIncomeItem->artist_contract_id;

            $contractHash{$contractID}{$trackID} = 1;
        }
    }
    else
    {
        my $mapItems = RPS::DB::Item::SaleRunMap->GetPaidArtistRoyaltyBySaleID($saleID);
        while (my $mapItem = $mapItems->next())
        {
            my $trackID = 0;
            my $contractID = 0;

            # Not all of these entries will _have_ a statement item id.
            # Data we pulled from the old sale_artist_royalty_run_map table, for example.
            #
            next unless $mapItem->statement_item_id;

            my $originalIncomeItem = RPS::DB::Item::ArtistRoyaltyIncomeItem->Lookup(artist_royalty_income_item_id => $mapItem->statement_item_id);
            if (! $originalIncomeItem)
            {
                _report("ERROR - cannot find original income item for sale map item: " . Dumper($mapItem));
                next;
            }
            my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $originalIncomeItem->artist_contract_term_id);
            if (! $term)
            {
                _report("ERROR - cannot find associated term for sale map item: " . Dumper($mapItem));
                next;
            }

            # We want to determine the _track_ that was associated with this payment, if any.
            # This is because in theory the same contract can be attached to multiple tracks on
            # a single album.  So, we want to make sure we are able to distinguish between new
            # track-contracts and old track-contracts for album level sales.
            #
            if ($originalIncomeItem->track_id)
            {
                $trackID = $originalIncomeItem->track_id;
            }
            $contractID = $term->artist_contract_id;

            $contractHash{$contractID}{$trackID} = 1;
        }

    }

    return \%contractHash;
}



sub _getLicenseIncomeData
{
    my ($saleID) = @_;

    my $data = RPS::DB::Item::LicenseIncome->Lookup(sale_id => $saleID);
    return $data;
}