#!/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";
    }
}