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

# select * from mechanical_statement_row left join mechanical_statement_cell on (mechanical_statement_row.mechanical_statement_row_id=mechanical_statement_cell.mechanical_statement_row_id) where mechanical_statement_id=15 and mechanical_statement_row.payee_index >= 10 and mechanical_statement_row.payee_index <= 20;

#use warnings;

use Data::Dumper;
use Getopt::Std;
use Carp;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::RSMath;
use Common::Locale;
use Common::Client;
use Common::Log;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Album;
use RPS::DB::Item::Channel;
use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::MechanicalStatementCrossedLicenseTransaction;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::MechanicalRun;
use RPS::DB::Item::MechanicalStatement;
use RPS::DB::Item::MechanicalStatementCell;
use RPS::DB::Item::MechanicalStatementItem;
use RPS::DB::Item::MechanicalStatementLicense;
use RPS::DB::Item::MechanicalStatementLicenseTransaction;
use RPS::DB::Item::MechanicalStatementPublisher;
use RPS::DB::Item::MechanicalStatementPublisherTransaction;
use RPS::DB::Item::MechanicalStatementRow;
use RPS::DB::Item::MechanicalStatementTrack;
use RPS::DB::Item::MechanicalStatementTransaction;
use RPS::DB::Item::Payor;
use RPS::DB::Item::PriceLevel;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductType;
use RPS::DB::Item::Publisher;
use RPS::DB::Item::Region;
use RPS::DB::Item::StatRate;

use lib '/app/tools/rps/bin/statements';
use Formats;

# !!! ENABLE DEBUGGING
#
#$ENV{DEV_SERVER} = 1;

$SIG{__DIE__} = \&Carp::confess;

#
# A lot of this code is taken from this tutorial: http://rick.measham.id.au/pdf-api2/
#

# These constants will make it easier to convert between 'regular' units
# and postscript points.
# There are 72 postscript points in an inch, and 25.4 millimeters in an inch.
# All the PDF::API2 methods expect their size or coordinate arguments to be in points.
#
use constant mm => ( 25.4 / 72 );
use constant in => ( 1 / 72 );
use constant pt => 1;

# The standard page dimensions.  Note that we're creating _landscape_ docs.
#
use constant kPageHeight => ( 8.5 / in );
use constant kPageWidth  => ( 11 / in );

# The margins of the page.
# Remember that (0,0) in PDF coordinates is the bottom-left of the page.
#
use constant kLeftMargin   => 0.5 / in;
use constant kBottomMargin => 0.5 / in;
use constant kRightMargin  => ( kPageWidth - ( 0.5 / in ) );
use constant kTopMargin    => ( kPageHeight - ( 0.5 / in ) );

# We are going to keep track of every page we create, so that we
# can go back later and add page numbers
#
my @gPages;

# We'll store a hashref with font info here, once we have a PDF object to work with.
#
my $font;

# We'll store the bookmarks here if I can ever figure out how this works
#
my $outline_root;

# This determines how much output we spew forth to STDERR.
# The user can set this with the -V command-line argument.
#
my $gVerbosityLevel = 1;

# Parse the command-line, then create the PDF!
#
my %options;
parseCommandLine( \%options );
createStatement( $options{clientID}, $options{mechanicalStatementID} );

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

sub createStatement {
    my ( $clientID, $mechanicalStatementID ) = @_;

    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new( clientID => $clientID );
    Common::Log::DebugMem('instantiated singleton');

    # We need to get rid of any old data hanging out for this statement.
    #
    Common::Log::DebugMem('deleting old statement data');
    RPS::DB::Item::MechanicalStatementCell->DeleteByMechanicalStatementID($mechanicalStatementID);
    RPS::DB::Item::MechanicalStatementRow->DeleteByMechanicalStatementID($mechanicalStatementID);

    # Grab the statement item and off we go.
    #
    my $statementItem = RPS::DB::Item::MechanicalStatement->Lookup( mechanical_statement_id => $mechanicalStatementID );
    Common::Log::DebugMem('instantiated RPS::DB::Item::MechanicalStatement');

    # !!! So, all I really need to keep track of this stuff properly is
    # the indexes.

    my ( $payeeIndex, $fullIndex );

    # Each section of the statement will have its own subroutine.
    # Note that I am passing the two indexes by reference, so we can
    # increment them as we go.
    #
    Common::Log::DebugMem('calling header');
    header( $statementItem, \$payeeIndex, \$fullIndex );
    Common::Log::DebugMem('calling summary');
    summary( $statementItem, \$payeeIndex, \$fullIndex );

    # Set up the header
    #
    addRow( $statementItem, undef, \$fullIndex, 'LEFT_TEXT', 'BOLD', { value => 'Current Period Details:' } );

    addRow(
        $statementItem,
        undef,
        \$fullIndex,
        'DETAILS_TABLE',
        'UNDERLINE OVERLINE BOLD REPEAT',
        { value => undef },
        { value => 'Track Title', class => 'BOLD' },
        { value => 'Album Title', class => 'BOLD' },
        { value => 'UPC', class => 'BOLD CENTER' },
        { value => 'Product Config', class => 'BOLD CENTER' },
        { value => 'Region', class => 'BOLD' },
        { value => 'Rate Period', class => 'BOLD CENTER' },
        { value => 'Base Rate', class => 'BOLD RIGHT' },
        { value => '% of Rate', class => 'BOLD RIGHT' },
        { value => 'Share', class => 'BOLD RIGHT' },
        { value => 'Net Rate', class => 'BOLD RIGHT' },
        { value => 'Gross Units', class => 'BOLD RIGHT' },
        { value => '% of Sales', class => 'BOLD RIGHT' },
        { value => 'Free Goods', class => 'BOLD RIGHT' },
        { value => 'Misc.', class => 'BOLD RIGHT' },
        { value => 'Reserves', class => 'BOLD RIGHT' },
        { value => 'Liquidations', class => 'BOLD RIGHT' },
        { value => 'Returns', class => 'BOLD RIGHT' },
        { value => 'Carryover', class => 'BOLD RIGHT' },
        { value => 'Net Units', class => 'BOLD RIGHT' },
        { value => 'Amount Due', class => 'BOLD RIGHT' },
    );

    # Determine if this is an admin or regular publisher.
    my $publisherItem = RPS::DB::Item::Publisher->Lookup( publisher_id => $statementItem->publisher_id );

    if ( $publisherItem->is_admin || $publisherItem->is_agency ) {

        # Loop through all publishers for admins.
        #
        my $publisherList = RPS::DB::Item::MechanicalStatementPublisher->GetSortedByMechanicalStatementID($mechanicalStatementID);
        Common::Log::DebugMem('got statement publisher list');
        while ( my $subPublisherItem = $publisherList->next() ) {

            # !!! wait, we pass in 'publisher'?
            # Well, we pass in 'mechanical_statement_publisher'.  It's a little different.
            Common::Log::DebugMem('calling details w/sub publisher');
            details( $subPublisherItem, \$payeeIndex, \$fullIndex );
        }
    } else {

        # Not an admin, so just do this once.
        Common::Log::DebugMem('calling details');
        details( $statementItem, \$payeeIndex, \$fullIndex );
    }
}

sub header {
    my ( $statement, $payeeIndexRef, $fullIndexRef ) = @_;

    Common::Log::DebugMem(' entering header');

    # Step 1 - Put the text 'United States Mechanical Royalty Statement' at the top of the page.
    #
    addRow( $statement, $payeeIndexRef, undef, 'STATEMENT_HEADER', undef, { value => 'United States Mechanical Royalty Statement' } );

    # Now print the run label. Which means we need to fetch the run...
    #
    my $run = RPS::DB::Item::MechanicalRun->Lookup( mechanical_run_id => $statement->mechanical_run_id );

    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'LEFT_TEXT', 'BOLD',  { value => $run->label } );
    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK',     'BLANK', { value => '' } );

    # Add a blank line.
    #
    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );

    # Add links to download PDF and text versions (for full statement only)
    #
    addRow(
        $statement,
        undef,
        $fullIndexRef,
        'LEFT_TEXT',
        undef,
        {
            value => '<img src="/production/images/pdficon_small.gif" class="pdfIcon" />Publisher View',
            link  => '/rps/statement?c=pdf_mechanical&MechanicalStatementID=' . $statement->mechanical_statement_id,
            title => 'Download PDF'
        }
    );

    addRow( $statement, undef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );

    addRow(
        $statement,
        undef,
        $fullIndexRef,
        'LEFT_TEXT',
        undef,
        {
            value => '<img src="/production/images/icons/office/Document-(16x16).gif" class="pdfIcon" />Electronic Format',
            link  => '/rps/statement?c=text_mechanical&MechanicalStatementID=' . $statement->mechanical_statement_id,
            title => 'Download Electronic Format'
        }
    );

    addRow( $statement, undef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );

    # Print the 'From: Payor' info - This _only_ appears on the 'Payee' version of the statement.
    #
    my $payor = RPS::DB::Item::Payor->Lookup( payor_id => $statement->payor_id );

    my $cityStateLine = $payor->city;
    if ( $payor->city && $payor->state_province ) {
        $cityStateLine .= ",";
    }
    $cityStateLine .= $payor->state_province . " " . $payor->postal_code;

    addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', 'BOLD', { value => 'From:' } );
    addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', undef,  { value => $payor->name } );

    if ( $payor->street_address ) {
        addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', undef, { value => $payor->street_address } );
    }
    if ( $payor->city || $payor->state_province ) {
        addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', undef, { value => $cityStateLine } );
    }

    addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', undef, { value => $payor->country_code } );

    addRow( $statement, $payeeIndexRef, undef, 'BLANK', 'BLANK', { value => '' } );

    # Now we add the 'To: Payee' stuff.
    #
    addRow( $statement, $payeeIndexRef, undef, 'LEFT_TEXT', 'BOLD', { value => 'To:' } );

    my $payee = RPS::DB::Item::Publisher->Lookup( publisher_id => $statement->publisher_id );

    my $cityStateLine = $payee->city;
    if ( $payee->city && $payee->state_province ) {
        $cityStateLine .= ",";
    }
    $cityStateLine .= $payee->state_province . " " . $payee->postal_code;

    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'LEFT_TEXT', undef,
        { value => $payee->publisher_name, link => '/rps/publisher?c=show&PublisherID=' . $payee->publisher_id } );
    if ( $payee->street_address ) {
        my @streetAddressLines = $payee->street_address . split('\n');
        foreach my $streetAddressLine (@streetAddressLines) {
            addRow( $statement, $payeeIndexRef, $fullIndexRef, 'LEFT_TEXT', undef, { value => $streetAddressLine } );
        }
    }
    if ( $payee->city || $payee->state_province ) {
        addRow( $statement, $payeeIndexRef, $fullIndexRef, 'LEFT_TEXT', undef, { value => $cityStateLine } );
    }
    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'LEFT_TEXT', undef, { value => $payee->country_code } );

    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );

    Common::Log::DebugMem(' exiting header');
}

sub summary {
    my ( $statement, $payeeIndexRef, $fullIndexRef ) = @_;
    Common::Log::DebugMem(' entering summary');

    my $payee = RPS::DB::Item::Publisher->Lookup( publisher_id => $statement->publisher_id );

    drawBalanceTable( $statement, $payeeIndexRef, $fullIndexRef, $payee->status );

    # Determine if this is an admin (or an agent) or regular publisher.
    if ( $payee->is_admin == 1 || $payee->is_agency == 1 ) {
        addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );
        drawPublisherTable( $statement, $payeeIndexRef, $fullIndexRef );
    }
    Common::Log::DebugMem(' exiting summary');
}

sub drawBalanceTable {
    my ( $statement, $payeeIndexRef, $fullIndexRef, $payeeStatus ) = @_;

    Common::Log::DebugMem(' entering drawBalanceTable');

    # The column headers
    #
    addRow(
        $statement, $payeeIndexRef, $fullIndexRef, 'BALANCE_TABLE', 'BOLD',
        { value => '',        class => 'BLANK' },        # !!! Is the 'class' necessary, really?
        { value => 'Amount',  class => 'BOLD RIGHT' },
        { value => 'Check #', class => 'BOLD' },
        { value => 'Date',    class => 'BOLD' },
        { value => 'Memo',    class => 'BOLD' },
    );

    # The first line
    #
    addRow(
        $statement,
        $payeeIndexRef,
        $fullIndexRef,
        'BALANCE_TABLE',
        undef,
        { value => 'Previous Period Balance:',                  class => 'BOLD' },
        { value => formatMoney( $statement->previous_balance ), class => 'RIGHT' },
        { value => ' ',                                         class => 'BLANK' },
        { value => ' ',                                         class => 'BLANK' },
        { value => ' ',                                         class => 'BLANK' },
    );

    # Get any transactions
    #
    my $transactionCollection =
      RPS::DB::Item::MechanicalStatementTransaction->GetByMechanicalStatementID( $statement->mechanical_statement_id );

    while ( $transactionCollection->hasNext() ) {
        my $transaction = $transactionCollection->next();

        my $type;
        if ( $transaction->type_code == 2 ) {
            $type = 'Adjustment';
        } elsif ( $transaction->type_code == 3 ) {
            $type = 'Advance';
        } elsif ( $transaction->type_code == 4 ) {
            $type = 'Payment';
        }
        my $amount = formatMoney( $transaction->amount );
        $amount = ' ' unless defined $amount;
        my $checkNum = $transaction->check_number;
        $checkNum = ' ' unless defined $checkNum;
        my $date = $transaction->transaction_date;
        $date = ' ' unless defined $date;
        my $memo = $transaction->memo;
        $memo = ' ' unless defined $memo;

        addRow(
            $statement, $payeeIndexRef, $fullIndexRef, 'BALANCE_TABLE',
            undef, { value => $type }, { value => $amount }, { value => $checkNum },
            { value => $date }, { value => $memo },
        );
    }

    addRow(
        $statement,    $payeeIndexRef,
        $fullIndexRef, 'BALANCE_TABLE',
        'UNDERLINE_0_1', { value => 'Current Period Royalties:', class => 'BOLD' },
        { value => formatMoney( $statement->subtotal ), class => 'RIGHT' }, { value => ' ', class => 'BLANK' },
        { value => ' ',                                 class => 'BLANK' }, { value => ' ', class => 'BLANK' },
    );

    if ( $statement->previous_advance_balance != 0 ) {
        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'BALANCE_TABLE',
            undef,
            { value => 'Previous Advance Balance:',                         class => 'BOLD' },
            { value => formatMoney( $statement->previous_advance_balance ), class => 'RIGHT' },
            { value => ' ',                                                 class => 'BLANK' },
            { value => ' ',                                                 class => 'BLANK' },
            { value => ' ',                                                 class => 'BLANK' },
        );

        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'BALANCE_TABLE',
            undef,
            { value => 'Applied To Advance:',                         class => 'BOLD' },
            { value => formatMoney( $statement->applied_to_advance ), class => 'RIGHT' },
            { value => ' ',                                           class => 'BLANK' },
            { value => ' ',                                           class => 'BLANK' },
            { value => ' ',                                           class => 'BLANK' },
        );

        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'BALANCE_TABLE',
            'UNDERLINE_0_1 BOLD',
            { value => 'Current Advance Balance:',                 class => 'BOLD' },
            { value => formatMoney( $statement->advance_balance ), class => 'RIGHT' },
            { value => ' ',                                        class => 'BLANK' },
            { value => ' ',                                        class => 'BLANK' },
            { value => ' ',                                        class => 'BLANK' },
        );
    }

    # figure out what value to use for Ending Balance
    my $endingBalance;
    if ( $statement->balance != 0 ) {
        $endingBalance = $statement->balance;
    } elsif ( $statement->amount_due != 0 ) {
        $endingBalance = $statement->amount_due;
    } else {
        $endingBalance = $statement->statement_total;
    }

    addRow(
        $statement,
        $payeeIndexRef,
        $fullIndexRef,
        'BALANCE_TABLE',
        'BOLD',
        { value => 'Ending Balance:',           class => 'BOLD OVERLINE' },
        { value => formatMoney($endingBalance), class => 'BOLD RIGHT OVERLINE' },
        { value => ' ',                         class => 'BLANK' },
        { value => ' ',                         class => 'BLANK' },
        { value => ' ',                         class => 'BLANK' },
    );

    addRow(
        $statement,    $payeeIndexRef,
        $fullIndexRef, 'BALANCE_TABLE',
        undef, { value => ' ', class => 'BLANK' },
        { value => ' ', class => 'BLANK' }, { value => ' ', class => 'BLANK' },
        { value => ' ', class => 'BLANK' }, { value => ' ', class => 'BLANK' },
    );

    addRow(
        $statement,    $payeeIndexRef,
        $fullIndexRef, 'BALANCE_TABLE',
        undef, { value => 'Minimum Payment:', class => 'BOLD' },
        { value => formatMoney( $statement->min_payment ), class => 'RIGHT' }, { value => ' ', class => 'BLANK' },
        { value => ' ',                                    class => 'BLANK' }, { value => ' ', class => 'BLANK' },
    );

    my $amountPayable;
    if ( $payeeStatus == 2 ) {
        $amountPayable = 'ON-HOLD';
    } else {
        $amountPayable = formatMoney( $statement->amount_due );
    }

    addRow(
        $statement,    $payeeIndexRef,
        $fullIndexRef, 'BALANCE_TABLE',
        'BOLD', { value => 'Amount Payable:', class => 'BOLD' },
        { value => $amountPayable, class => 'BOLD RIGHT' }, { value => '(USD)' },
        { value => ' ', class => 'BLANK' }, { value => ' ', class => 'BLANK' },
    );

    addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );

    Common::Log::DebugMem(' exiting drawBalanceTable');
}

sub drawPublisherTable {
    my ( $statement, $payeeIndexRef, $fullIndexRef ) = @_;
    Common::Log::DebugMem(' entering drawPublisherTable');

    # Get any publishers
    #
    my $publisherList =
      RPS::DB::Item::MechanicalStatementPublisher->GetSortedByMechanicalStatementID( $statement->mechanical_statement_id );
    if ( $publisherList->hasNext() ) {

        addRow(
            $statement, $payeeIndexRef, $fullIndexRef, 'PUBLISHER_TABLE',
            'BOLD UNDERLINE_0_1',
            { value => 'Publisher',  class => 'BOLD' },
            { value => 'Amount Due', class => 'BOLD' },
        );

        while ( my $publisher = $publisherList->next() ) {
            my $publisherItem = RPS::DB::Item::Publisher->Lookup( publisher_id => $publisher->publisher_id );
            my $name          = $publisherItem->publisher_name();
            my $amount        = formatMoney( $publisher->amount_due() );
            $amount = ' ' unless defined $amount;

            # We're going to set a temporary value for the link.
            # We'll come back later when we know which row the publisher's statement starts on.
            #
            addRow(
                $statement, $payeeIndexRef, $fullIndexRef, 'PUBLISHER_TABLE', undef,
                { value => $name,   link  => 'REPLACE_' . $publisher->publisher_id },
                { value => $amount, class => 'RIGHT' },
            );
        }

        addRow( $statement, $payeeIndexRef, $fullIndexRef, 'BLANK', 'BLANK', { value => '' } );
    }

    Common::Log::DebugMem(' exiting drawPublisherTable');
}

sub details {
    my ( $statement, $payeeIndexRef, $fullIndexRef ) = @_;

    Common::Log::DebugMem(' entering details');

    # We need to know if this is an admin statement
    my $whatAmI = ref($statement);

    # If Admin, print this Publisher's Name at the top.
    if ( 'RPS::DB::Item::MechanicalStatementPublisher' eq $whatAmI ) {
        my $publisherItem = RPS::DB::Item::Publisher->Lookup( publisher_id => $statement->publisher_id );

        # For the PDF
        #
        addRow( $statement, $payeeIndexRef, undef, 'SUBPUBLISHER_TABLE', 'BOLD', { value => $publisherItem->publisher_name() }, );
        addRow(
            $statement,
            $payeeIndexRef,
            undef,
            'DETAILS_TABLE',
            'UNDERLINE_0_9',
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
            { value => undef },
        );

        # And for the web page
        #
        addRow(
            $statement,
            undef,
            $fullIndexRef,
            'DETAILS_TABLE',
            'BOLD',
            { value => undef },
            {
                value => $publisherItem->publisher_name() . '<a name="' . $statement->publisher_id . '" />',
                link  => '/rps/publisher?c=show&PublisherID=' . $statement->publisher_id,
                class => 'PUBLISHER_ROW'
            },
        );

        # And now update the link in the publisher table.
        #
        my $rowIndex = $$fullIndexRef - 1;
        my $oldLink  = "REPLACE_" . $statement->publisher_id;
        my $newLink =
            "/rps/statement?c=show_mechanical_page&row="
          . $rowIndex
          . "&MechanicalStatementID="
          . $statement->mechanical_statement_id . "#"
          . $statement->publisher_id;
        RPS::DB::Item::MechanicalStatementCell->UpdateMechanicalStatementCellLink( $oldLink, $newLink,
            $statement->mechanical_statement_id );

    }

    # In the payee version, we're going to repeat this for every payee in the statement.
    # We don't want that to happen in the full version, so we'll set that up elsewhere.

    addRow(
        $statement,
        $payeeIndexRef,
        undef,
        'DETAILS_TABLE',
        'UNDERLINE_0_9 BOLD REPEAT',
        { value => 'Track Title',    class => 'BOLD' },
        { value => 'Album Title',    class => 'BOLD' },
        { value => 'UPC',            class => 'BOLD CENTER' },
        { value => 'Product Config', class => 'BOLD CENTER' },
        { value => 'Region',         class => 'BOLD' },
        { value => 'Rate Period',    class => 'BOLD CENTER' },
        { value => 'Share',          class => 'BOLD RIGHT' },
        { value => 'Net Rate',       class => 'BOLD RIGHT' },
        { value => 'Net Units',      class => 'BOLD RIGHT' },
        { value => 'Amount Due',     class => 'BOLD RIGHT' },
    );

    # Add the track details.
    #
    my $statementTrackCollection;
    if ( 'RPS::DB::Item::MechanicalStatementPublisher' eq $whatAmI ) {
        Common::Log::DebugMem( ' fetching StatementTrackList w/ publisher_id:' . $statement->publisher_id );
        $statementTrackCollection =
          RPS::DB::Item::MechanicalStatementTrack->GetSortedByMechanicalStatementIDPublisherID( $statement->mechanical_statement_id,
            $statement->publisher_id );
    } else {
        Common::Log::DebugMem(' fetching StatementTrackList');
        $statementTrackCollection =
          RPS::DB::Item::MechanicalStatementTrack->GetSortedByMechanicalStatementID( $statement->mechanical_statement_id );
    }

    while ( $statementTrackCollection->hasNext() ) {
        my $statementTrack = $statementTrackCollection->next();
        trackDetails( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack );
    }

    # Last, print the grand total
    #

    my @totalRows;
    my @totalRowProps;

    if ( 'RPS::DB::Item::MechanicalStatementPublisher' eq $whatAmI ) {
        my $previousBalance = $statement->previous_balance();

        # Add the transactions in.
        #
        my $transactionCollection = RPS::DB::Item::MechanicalStatementPublisherTransaction->GetByMechanicalStatementAndPublisherID(
            $statement->mechanical_statement_id,
            $statement->publisher_id );

        my $transactionCount = RPS::DB::Item::MechanicalStatementPublisherTransaction->GetCountByMechanicalStatementAndPublisherID(
            $statement->mechanical_statement_id,
            $statement->publisher_id );

        if ( $previousBalance != 0 || $transactionCount > 0 ) {

            addRow( $statement, $payeeIndexRef, $fullIndexRef, 'DETAILS_TABLE', 'BLANK', { value => undef, class => 'BLANK_ROW' }, );

            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => 'Publisher Subtotal:',               class     => 'SUBTOTAL_ROW' },
                { value => formatMoney( $statement->subtotal ), class     => 'RIGHT' },
            );

            if ( $previousBalance != 0 ) {
                addRow(
                    $statement,
                    $payeeIndexRef,
                    $fullIndexRef,
                    'DETAILS_TABLE',
                    undef,
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => undef,                                       payeeOnly => 1 },
                    { value => 'Previous Balance:',                         class     => 'SUBTOTAL_ROW' },
                    { value => formatMoney( $statement->previous_balance ), class     => 'RIGHT' },
                );
            }

            while ( $transactionCollection->hasNext() ) {
                my $transaction = $transactionCollection->next();
                my $type;
                if ( $transaction->type_code == 2 ) {
                    $type = 'Adjustment';
                } elsif ( $transaction->type_code == 3 ) {
                    $type = 'Advance';
                } elsif ( $transaction->type_code == 4 ) {
                    $type = 'Payment';
                }
                addRow(
                    $statement,
                    $payeeIndexRef,
                    $fullIndexRef,
                    'DETAILS_TABLE',
                    undef,
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => undef,                               payeeOnly => 1 },
                    { value => $type,                               class     => 'SUBTOTAL_ROW' },
                    { value => formatMoney( $transaction->amount ), class     => 'RIGHT' },
                );
            }

            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                'UNDERLINE_9 BOLD',
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => undef,                                      payeeOnly => 1 },
                { value => 'Ending Balance:',                          class     => 'SUBTOTAL_ROW' },
                { value => formatMoney( $statement->statement_total ), class     => 'OVERLINE BOLD RIGHT' },
            );
        }

        addRow( $statement, $payeeIndexRef, $fullIndexRef, 'DETAILS_TABLE', 'BLANK', { value => undef, class => 'BLANK_ROW' }, );

        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'DETAILS_TABLE',
            'BOLD',
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => undef,                                 payeeOnly => 1 },
            { value => 'Publisher Amount Due:',               class     => 'SUBTOTAL_ROW' },
            { value => formatMoney( $statement->amount_due ), class     => 'BOLD RIGHT' },
        );

    } else {
        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'DETAILS_TABLE',
            'OVERLINE BOLD',
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => 'Total:',                            class     => 'SUBTOTAL_ROW' },
            { value => formatMoney( $statement->subtotal ), class     => 'RIGHT' },
        );
    }

    addRow( $statement, $payeeIndexRef, undef, 'BLANK', 'BLANK', { value => '' } );

    Common::Log::DebugMem(' exiting details');
}

sub trackDetails {
    my ( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack ) = @_;
    Common::Log::DebugMem(' entering trackDetails');

    crossedLicenseDetails( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack );
    licenseDetails( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack );
    Common::Log::DebugMem(' exiting trackDetails');
}

sub crossedLicenseDetails {
    my ( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack ) = @_;
    Common::Log::DebugMem(' entering crossedLicenseDetails');

    my $album = RPS::DB::Item::Album->Lookup( album_id => $statementTrack->album_id );
    my $track = RPS::DB::Item::Track->Lookup( track_id => $statementTrack->track_id );

    # Go through the entire (crossed) license list
    #
    my $licenseCollection =
      RPS::DB::Item::MechanicalStatementLicense->GetByMechanicalStatementIDTrackIDPublisherID( $statement->mechanical_statement_id,
        $statementTrack->track_id, $statement->publisher_id );

    my $licenseCount = 0;
    my $itemCount    = 0;

    my $lastRow;
    my $lastCellArray;

    while ( $licenseCollection->hasNext() ) {
        my $license = $licenseCollection->next();
        my $statementTrackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $license->track_license_id );

        next if ( !$license->crossed );

        $licenseCount++;

        my $incomeItemCollection =
          RPS::DB::Item::MechanicalStatementItem->GetByStatementAndTrackLicense( $statement->mechanical_statement_id,
            $license->track_license_id );

        while ( $incomeItemCollection->hasNext() ) {
            my $incomeItem = $incomeItemCollection->next();
            $itemCount++;

            # We have to check the license product type id to see
            # if we're dealing with a ringtone.
            #
            my $productTypeID = _checkForRingtone( $statementTrackLicense, $incomeItem );

            my $statRateIDToUse = $incomeItem->applied_stat_rate_id;
            if ( !$statRateIDToUse ) {
                $statRateIDToUse = $incomeItem->sale_stat_rate_id;
            }
            my $ratePeriod = _getRatePeriod($statRateIDToUse);

            my $rowClass = 'OVERLINE';
            if ( $itemCount > 1 ) {
                $rowClass = undef;
            }
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                $rowClass,
                {
                    value    => '<img src="/production/images/icons/mini/briefcase.gif" />',
                    link     => '/rps/license?c=show&TrackLicenseID=' . $license->track_license_id,
                    title    => 'View License',
                    fullOnly => 1
                },
                { value => $track->title,                     link  => '/rps/track?c=show&TrackID=' . $statementTrack->track_id },
                { value => $album->title,                     link  => '/rps/catalog?c=show&AlbumID=' . $statementTrack->album_id },
                { value => $incomeItem->upc,                  class => 'CENTER' },
                { value => productCodeToName($productTypeID), class => 'CENTER' },
                { value => regionIDToName( $license->region_id ), },
                { value => $ratePeriod,                       class => 'CENTER' },
                { value => formatPercent( $incomeItem->base_rate ),                      class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->rate_percentage ),     class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->share ),               class => 'RIGHT' },
                { value => formatNumber( $incomeItem->net_rate ),                        class => 'RIGHT' },
                { value => formatNumber( $incomeItem->gross_units ),                     class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->percentage_of_sales ), class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->free_goods ),          class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->misc_deduction ),      class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->reserved ),                        class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->liquidated ),                      class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->returns ),                         class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->carryover ),                       class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->net_units ),                       class => 'RIGHT' },
                { value => formatMoney( $incomeItem->amount_paid ),                      class => 'RIGHT' },
            );
        }

        if ( $itemCount == 0 ) {
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                'OVERLINE',
                {
                    value    => '<img src="/production/images/icons/mini/briefcase.gif" />',
                    link     => '/rps/license?c=show&TrackLicenseID=' . $license->track_license_id,
                    title    => 'View License',
                    fullOnly => 1
                },
                { value => $statementTrack->title, link     => '/rps/track?c=show&TrackID=' . $statementTrack->track_id },
                { value => $album->title,          link     => '/rps/catalog?c=show&AlbumID=' . $statementTrack->album_id },
                { value => undef, },
                { value => undef, },
                { value => regionIDToName( $license->region_id ), },
                { value => undef, },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef, },
                { value => undef, },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef, },
                { value => undef, },
            );
        }

        if ($lastRow) {
            my $lastRowClass = $lastRow->class();
            if ($lastRowClass) {
                $lastRowClass .= ' UNDERLINE_9';
            } else {
                $lastRowClass = 'UNDERLINE_9';
            }
            $lastRow->class($lastRowClass);
            $lastRow->save();
        }

        $lastRow       = undef;
        $lastCellArray = undef;

        # Add the license summary stuff.
        #
        # We are going to change a lot of things if there is no previous balance
        # OR license transactions.  So let's check for them now.

        my $previousBalance = $license->previous_advance_balance;

        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'DETAILS_TABLE',
            undef,
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => undef,                              payeeOnly => 1 },
            { value => 'Current Period License Subtotal:', class     => 'SUBTOTAL_ROW' },
            { value => formatMoney( $license->subtotal ),  class     => 'RIGHT OVERLINE' },
        );

        addRow( $statement, $payeeIndexRef, $fullIndexRef, 'DETAILS_TABLE', undef, { value => undef, class => 'BLANK_ROW' }, );

        # Add the transactions in.
        #
        my $transactionCollection = RPS::DB::Item::MechanicalStatementLicenseTransaction->GetByMechanicalStatementLicenseID(
            $license->mechanical_statement_license_id );

        my $transactionCount = 0;

        while ( $transactionCollection->hasNext() ) {
            my $transaction = $transactionCollection->next();
            $transactionCount++;

            my $transactionType;
            if ( $transaction->type_code == 3 ) {
                $transactionType = 'Advance:';
            } else {
                $transactionType = 'Adjustment:';
            }

            my $memo = ' ';
            if ( $transaction->memo ) {
                $memo = '(' . $transaction->memo . ') ';
            }
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => $memo . $transactionType,            class     => 'SUBTOTAL_ROW' },
                { value => formatMoney( $transaction->amount ), class     => 'RIGHT' },
            );
        }

        if ( ( $previousBalance != 0 ) || ( $license->advance_balance != 0 ) ) {
            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => 'Previous License Balance:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($previousBalance), class     => 'RIGHT' },
            );

            my $licenseBalance = $license->advance_balance;

            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'License Balance Forward:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'OVERLINE UNDERLINE RIGHT' },
            );
        }

        # And really, truly finally, the adjusted subtotal if need be.
        #
        if ( ( $transactionCount != 0 ) || ( $previousBalance != 0 ) || ( $license->subtotal != $license->adjusted_subtotal ) ) {
            my $licenseBalance = $license->adjusted_subtotal;
            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'License Adjusted Subtotal:', class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'RIGHT' },
            );

            addRow( $statement, $payeeIndexRef, $fullIndexRef, 'DETAILS_TABLE', undef, { value => undef, class => 'BLANK_ROW' }, );

        }

    }

    if ( $licenseCount > 0 ) {

        # Add the license summary stuff.
        #
        # We are going to change a lot of things if there is no previous balance
        # OR license transactions.  So let's check for them now.
        my $previousBalance = $statementTrack->crossed_previous_advance_balance();

        ( $lastRow, $lastCellArray ) = addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'DETAILS_TABLE',
            undef,
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => undef,                                              payeeOnly => 1 },
            { value => 'Current Period Crossed Subtotal:',                 class     => 'SUBTOTAL_ROW' },
            { value => formatMoney( $statementTrack->crossed_subtotal() ), class     => 'RIGHT' },
        );

        # Add the transactions in.
        #
        my $crossedTransactionCollection =
          RPS::DB::Item::MechanicalStatementCrossedLicenseTransaction->GetByMechanicalStatementIDPublisherIDTrackID(
            $statement->mechanical_statement_id,
            $statement->publisher_id, $statementTrack->track_id );

        my $crossedTransactionCount = 0;

        while ( $crossedTransactionCollection->hasNext() ) {
            my $crossedTransaction = $crossedTransactionCollection->next();
            $crossedTransactionCount++;

            my $transactionType;
            if ( $crossedTransaction->TypeCode == 3 ) {
                $transactionType = 'Advance:';
            } else {
                $transactionType = 'Adjustment:';
            }

            my $memo = ' ';
            if ( $crossedTransaction->memo ) {
                $memo = '(' . $crossedTransaction->memo . ') ';
            }

            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => undef,                                        payeeOnly => 1 },
                { value => $memo . $transactionType,                     class     => 'SUBTOTAL_ROW' },
                { value => formatMoney( $crossedTransaction->amount() ), class     => 'RIGHT' },
            );
        }

        my $showCrossedAdjustedSubtotal;
        if (   ( $crossedTransactionCount != 0 )
            || ( $previousBalance != 0 )
            || ( $statementTrack->crossed_subtotal != $statementTrack->crossed_adjusted_subtotal ) ) {
            $showCrossedAdjustedSubtotal = 1;
        } else {
            $showCrossedAdjustedSubtotal = 0;
        }

        if ( ( $previousBalance != 0 ) || ( $statementTrack->crossed_advance_balance != 0 ) ) {
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => 'Previous Crossed Balance:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($previousBalance), class     => 'RIGHT' },
            );

            my $licenseBalance = $statementTrack->crossed_advance_balance;
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'Crossed Balance Forward:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'RIGHT' },
            );
        }

        # And really, truly finally, the adjusted subtotal if need be.
        #
        if ( $showCrossedAdjustedSubtotal == 1 ) {

            # We want a little line to show up in the PDF just before the next row.
            #
            if ($lastRow) {
                my $lastRowClass = $lastRow->class();
                if ($lastRowClass) {
                    $lastRowClass .= ' UNDERLINE_9';
                } else {
                    $lastRowClass = 'UNDERLINE_9';
                }
                $lastRow->class($lastRowClass);
                $lastRow->save();
            }

            my $licenseBalance = $statementTrack->crossed_adjusted_subtotal;
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'Crossed Adjusted Subtotal:', class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'RIGHT OVERLINE' },
            );
        }

        if ($lastRow) {
            my $lastRowClass = $lastRow->class();
            if ($lastRowClass) {
                $lastRowClass .= ' UNDERLINE_0_9';
            } else {
                $lastRowClass = 'UNDERLINE_0_9';
            }
            $lastRow->class($lastRowClass);
            $lastRow->save();
        }

    }

    Common::Log::DebugMem(' exiting crossedLicenseDetails');
}

sub licenseDetails {
    my ( $statement, $payeeIndexRef, $fullIndexRef, $statementTrack ) = @_;
    Common::Log::DebugMem(' entering licenseDetails');

    my $album = RPS::DB::Item::Album->Lookup( album_id => $statementTrack->album_id );
    my $track = RPS::DB::Item::Track->Lookup( track_id => $statementTrack->track_id );

    # Go through the entire (uncrossed) license list
    #
    my $licenseCollection =
      RPS::DB::Item::MechanicalStatementLicense->GetByMechanicalStatementIDTrackIDPublisherID( $statement->mechanical_statement_id,
        $statementTrack->track_id, $statement->publisher_id );

    my $licenseCount = 0;

    while ( $licenseCollection->hasNext() ) {
        my $license = $licenseCollection->next();
        my $statementTrackLicense = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $license->track_license_id );

        next if ( $license->crossed );

        $licenseCount++;

        my $incomeItemCollection =
          RPS::DB::Item::MechanicalStatementItem->GetByStatementAndTrackLicense( $statement->mechanical_statement_id,
            $license->track_license_id );

        my $lastRow;
        my $lastCellArray;
        my $itemCount = 0;

        while ( $incomeItemCollection->hasNext() ) {
            my $incomeItem = $incomeItemCollection->next();
            $itemCount++;

            # We have to check the license product type id to see
            # if we're dealing with a ringtone.
            #
            my $productTypeID = _checkForRingtone( $statementTrackLicense, $incomeItem );

            my $statRateIDToUse = $incomeItem->applied_stat_rate_id;
            if ( !$statRateIDToUse ) {
                $statRateIDToUse = $incomeItem->sale_stat_rate_id;
            }
            my $ratePeriod = _getRatePeriod($statRateIDToUse);

            my $rowClass = 'OVERLINE';
            if ( $itemCount > 1 ) {
                $rowClass = undef;
            }

            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                $rowClass,
                {
                    value    => '<img src="/production/images/icons/mini/briefcase.gif" />',
                    link     => '/rps/license?c=show&TrackLicenseID=' . $license->track_license_id,
                    title    => 'View License',
                    fullOnly => 1
                },
                { value => $track->title,                     link  => '/rps/track?c=show&TrackID=' . $statementTrack->track_id },
                { value => $album->title,                     link  => '/rps/catalog?c=show&AlbumID=' . $statementTrack->album_id },
                { value => $incomeItem->upc,                  class => 'CENTER' },
                { value => productCodeToName($productTypeID), class => 'CENTER' },
                { value => regionIDToName( $license->region_id ), },
                { value => $ratePeriod,                       class => 'CENTER' },
                { value => formatPercent( $incomeItem->base_rate ),                      class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->rate_percentage ),     class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->share ),               class => 'RIGHT' },
                { value => formatNumber( $incomeItem->net_rate ),                        class => 'RIGHT' },
                { value => formatNumber( $incomeItem->gross_units ),                     class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->percentage_of_sales ), class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->free_goods ),          class => 'RIGHT', fullOnly => 1 },
                { value => formatPercent( $statementTrackLicense->misc_deduction ),      class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->reserved ),                        class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->liquidated ),                      class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->returns ),                         class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->carryover ),                       class => 'RIGHT', fullOnly => 1 },
                { value => formatNumber( $incomeItem->net_units ),                       class => 'RIGHT' },
                { value => formatMoney( $incomeItem->amount_paid ),                      class => 'RIGHT' },
            );
        }

        if ( $itemCount == 0 ) {

            # Even if there are no income items,
            # we still want to display the track info.
            ( $lastRow, $lastCellArray ) = addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                'OVERLINE',
                {
                    value    => '<img src="/production/images/icons/mini/briefcase.gif" />',
                    link     => '/rps/license?c=show&TrackLicenseID=' . $license->track_license_id,
                    title    => 'View License',
                    fullOnly => 1
                },
                { value => $statementTrack->title, link     => '/rps/track?c=show&TrackID=' . $statementTrack->track_id },
                { value => $album->title,          link     => '/rps/catalog?c=show&AlbumID=' . $statementTrack->album_id },
                { value => undef, },
                { value => undef, },
                { value => regionIDToName( $license->region_id ), },
                { value => undef, },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef, },
                { value => undef, },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef,                  fullOnly => 1 },
                { value => undef, },
                { value => undef, },
            );
        }

        if ($lastRow) {
            my $lastRowClass = $lastRow->class();
            if ($lastRowClass) {
                $lastRowClass .= ' UNDERLINE_9';
            } else {
                $lastRowClass = 'UNDERLINE_9';
            }
            $lastRow->class($lastRowClass);
            $lastRow->save();
        }

        $lastRow       = undef;
        $lastCellArray = undef;

        # Add the license summary stuff.
        #
        # We are going to change a lot of things if there is no previous balance
        # OR license transactions.  So let's check for them now.
        my $previousBalance = $license->previous_advance_balance();

        addRow(
            $statement,
            $payeeIndexRef,
            $fullIndexRef,
            'DETAILS_TABLE',
            'UNDERLINE_0_9',
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => undef,                               payeeOnly => 1 },
            { value => 'Current Period License Subtotal:',, class     => 'SUBTOTAL_ROW' },
            { value => formatMoney( $license->subtotal ),   class     => 'RIGHT OVERLINE' },
        );

        # Add the transactions in.
        #
        my $transactionCollection = RPS::DB::Item::MechanicalStatementLicenseTransaction->GetByMechanicalStatementLicenseID(
            $license->mechanical_statement_license_id );

        my $transactionCount = 0;

        while ( $transactionCollection->hasNext() ) {
            my $transaction = $transactionCollection->next();
            $transactionCount++;

            my $transactionType;
            if ( $transaction->type_code == 3 ) {
                $transactionType = 'Advance:';
            } else {
                $transactionType = 'Adjustment:';
            }

            my $memo = ' ';
            if ( $transaction->memo ) {
                $memo = '(' . $transaction->memo . ') ';
            }

            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => undef,                               payeeOnly => 1 },
                { value => $memo . $transactionType,            class     => 'SUBTOTAL_ROW' },
                { value => formatMoney( $transaction->amount ), class     => 'RIGHT' },
            );
        }

        if ( ( $previousBalance != 0 ) || ( $license->advance_balance != 0 ) ) {
            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => undef,                         payeeOnly => 1 },
                { value => 'Previous License Balance:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($previousBalance), class     => 'RIGHT' },
            );

            my $licenseBalance = $license->advance_balance;

            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'License Balance Forward:',   class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'OVERLINE UNDERLINE RIGHT' },
            );
        }

        # And really, truly finally, the adjusted subtotal if need be.
        #
        if ( ( $transactionCount != 0 ) || ( $previousBalance != 0 ) || ( $license->subtotal != $license->adjusted_subtotal ) ) {
            my $licenseBalance = $license->adjusted_subtotal;
            addRow(
                $statement,
                $payeeIndexRef,
                $fullIndexRef,
                'DETAILS_TABLE',
                undef,
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => undef,                        payeeOnly => 1 },
                { value => 'License Adjusted Subtotal:', class     => 'SUBTOTAL_ROW' },
                { value => formatMoney($licenseBalance), class     => 'RIGHT' },
            );
        }
    }
    Common::Log::DebugMem(' exiting licenseDetails');
}

sub formatNumber {
    my ($value) = @_;

    $value = Formats::formatNumber($value);

    return $value;
}

sub formatMoney {
    my ($value) = @_;

    # Use our own rounding algorithm.
    #
    my $rVal = Common::RSMath::round( $value, 2 );

    $rVal = Common::Client::Current()->Locale()->formatMoney( $rVal, "USD" );

    return $rVal;

    #my ($value) = @_;

    #$value = Formats::formatMoney($value);

    #return $value;
}

sub formatPercent {
    my ($value) = @_;

    $value = Formats::formatPercent($value);

    return $value;
}

sub _checkForRingtone {
    my ( $license, $incomeItem ) = @_;

    my $productTypeID;

    if ( $license->product_type_id == RPS::DB::Item::Product::kProductTypeRingtone ) {
        $productTypeID = RPS::DB::Item::Product::kProductTypeRingtone;
    } else {
        my $product = RPS::DB::Item::Product->Lookup( product_id => $incomeItem->product_id );
        $productTypeID = $product->product_type_id;
    }

    return $productTypeID;
}

my $gRatePeriodMap;

sub _getRatePeriod {
    my ($id) = @_;

    return "" unless $id;

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

        my $statRates = RPS::DB::Item::StatRate->GetAll();
        while ( my $statRate = $statRates->next() ) {
            my ( $year, $month, $day ) = split( '-', $statRate->date_effective );
            my $nextYear;

            # Ringtones only have one stat rate, so we'll treat them differently.
            #
            if ( $statRate->stat_rate_type_id == 3 ) {
                $nextYear = 'Current';
            } else {
                if ( $year < 2006 ) {
                    $nextYear = $year + 1;
                } else {
                    $nextYear = 2011;
                }
            }
            my $range = "$year-$nextYear";
            $gRatePeriodMap->{ $statRate->stat_rate_id } = $range;
        }
    }

    return $gRatePeriodMap->{$id};
}

# These functions map ids to their text representations.
# Basically, the first time we ask for a particular id->string mapping, we
# will query the correct database, and build a hash.  Subsequent calls just
# hit the hash.
#
# Because these tables are so similar, I abstracted the guts into the _genericMapAccessor
# method (to save myself some typing).
#
my %idMaps;

sub _genericMapAccessor {
    my ( $collectionAccessor, $idName, $id ) = @_;

    if ( !defined $idMaps{$collectionAccessor} ) {
        $idMaps{$collectionAccessor} = {};

        # This is a very naughty thing to do, but it works great in this context.
        #
        #        no strict 'refs';
        #        my $c = &$collectionAccessor();
        #        use strict 'refs';
        my $c = $collectionAccessor->GetAll();

        while ( my $item = $c->next() ) {
            if ( $idName ne 'product_type_id' ) {
                $idMaps{$collectionAccessor}{ $item->$idName() } = $item->name;
            } else {
                $idMaps{$collectionAccessor}{ $item->$idName() } =
                  $item->description;
            }
        }
    }

    return $idMaps{$collectionAccessor}{$id};
}

sub incomeSourceIDToName {
    my ($id) = @_;

    return _genericMapAccessor( 'RPS::DB::Item::IncomeSource', 'income_source_id', $id );
}

sub regionIDToName {
    my ($id) = @_;

    # we don't want to show Rest of World here
    if ( $id != 0 ) {
        return _genericMapAccessor( 'RPS::DB::Item::Region', 'region_id', $id );
    } else {
        return ' ';
    }
}

sub channelIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::Channel', 'channel_id', $id );
}

sub priceLevelIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::PriceLevel', 'price_level_id', $id );
}

sub contractRateTypeIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::ContractRateType', 'contract_rate_type_id', $id );
}

sub productCodeToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::ProductType', 'product_type_id', $id );
}

sub addDraftWatermark {
    my ($pdf) = @_;

    my $centerX = kPageWidth / 2;
    my $centerY = kPageHeight / 2;

    foreach my $page (@gPages) {
        my $text = $page->text(1);
        $text->font( $font->{Helvetica}{Bold}, 128 / pt );
        $text->transform(
            -translate => [ $centerX, $centerY ],
            -rotate    => 45
        );

        $text->fillcolor('lightgray');
        $text->text_center('DRAFT');
        $text->fillcolor('black');
    }
}

sub addRow {
    my ( $statement, $payeeIndexRowRef, $fullIndexRowRef, $tableClass, $rowClass, @cells ) = @_;

    Common::Log::DebugMem( "   entering addRow $payeeIndexRowRef $fullIndexRowRef $tableClass $rowClass " . join( ",", @cells ) );

    # Create the new row
    #
    my $row = RPS::DB::Item::MechanicalStatementRow->Create( mechanical_statement_id => $statement->mechanical_statement_id(), );
    $row->payee_index( $$payeeIndexRowRef++ ) if defined $payeeIndexRowRef;
    $row->full_index( $$fullIndexRowRef++ )   if defined $fullIndexRowRef;
    $row->table_class($tableClass)            if defined $tableClass;
    $row->class($rowClass)                    if defined $rowClass;

    if ( $rowClass =~ m/REPEAT/ ) {
        $row->repeat_row(1);
    }

    $row->save();

    # Create the columns
    #
    my $payeeIndexCell = 0;
    my $fullIndexCell  = 0;
    my $rowID          = $row->mechanical_statement_row_id();

    my @returnCells;

    foreach my $cellData (@cells) {
        my $cell = RPS::DB::Item::MechanicalStatementCell->Create(
            mechanical_statement_row_id => $rowID,
            display_value               => $cellData->{value},
            class                       => $cellData->{class},
            link_value                  => $cellData->{link},
            title_value                 => $cellData->{title},
        );

        if ( !$cellData->{fullOnly} ) {
            $cell->payee_index( $payeeIndexCell++ );
        }
        if ( !$cellData->{payeeOnly} ) {
            $cell->full_index( $fullIndexCell++ );
        }

        $cell->save();

        push @returnCells, $cell;
    }

    # So we're going to return references to the row and cell items.
    # Usually we'll simply ignore that, but occasionally we need this to
    # make further changes.

    Common::Log::DebugMem('   exiting addRow');
    return ( $row, \@returnCells );
}

#
# Boring script stuff below...
#

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

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

    if ( !$opt{s} || !$opt{c} ) {
        usage();
        exit(1);
    }
    $settings->{mechanicalStatementID} = $opt{s};
    $settings->{clientID}              = $opt{c};

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

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -s <mechanical_statement_id> [-f <output file>]\n";
    print STDERR "\n";
    print STDERR "Arguments:\n";
    print STDERR "\t-c <client_id>\t\t\tThe client_id of the client to process\n";
    print STDERR "\t-s <mechanical_statement_id>\tThe id of the mechanical statement to convert\n";
}

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

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